SQL Server Performance Forum – Threads Archive

help with this procedure

hello
dis procedure is meant to update a table
with data fed thru an asp page
but it only updates the email field, this is fed by the @email parameter
d proceure:
ALTER procedure dbo.stp_updateprofile
(
@email1 varchar(50),
@email varchar(50),
@passwd varchar(50),
@nationality varchar(50),
@state varchar(50),
@lga varchar(50),
@street varchar(50),
@addresslga varchar(50),
@phone varchar(50) ,
@sex varchar(50)
)
as if
@email<>”
begin
update waecpast set email= @email where email= @email1
end
else
begin
rollback
end if
@passwd<>”
begin
update waecpast set [password]= @passwd where email= @email1
end
else
begin
rollback
end if
@nationality<>”
begin
update waecpast set nationality= @nationality where email= @email1
end
else
begin
rollback
end if
@state <>”
begin
update waecpast set state = @state where email= @email1
end
else
begin
rollback
end if
@lga<>”
begin
update waecpast set lga= @lga where email= @email1
end
else
begin
rollback
end if
@street<>”
begin
update waecpast set street= @street where email= @email1
end
else
begin
rollback
end if
@addresslga<>”
begin
update waecpast set addresslga= @addresslga where email= @email1
end
else
begin
rollback
end if
@phone<>”
begin
update waecpast set phone= @phone where email= @email1
end
else
begin
rollback
end if
@sex<>”
begin
update waecpast set sex= @sex where email= @email1
end
else
begin
rollback
end

Just out of curiosity. Can @sex ever be a VARCHAR(50)? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Are you passing an empty string, or NULL? – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

also what is the intention of this proc, since it would seem that the rollback statements in later if clauses could cause the transaction to only complete for the values further down in the proc… e.g. if @sex and @street given, then the if statements before the @sex may cause the street update to be rolled back? Cheers
Twan

I don’t like this type of writing at all, there is no logical transaction handling in this procedure and you update the same record several times while you can do it with one query (including the variables check). but anyway, it seems like you first update the email column to @email value, and then you have to compare the other updates to the new value (@email) instead of the original value (@email1).

>>Just out of curiosity. Can @sex ever be a VARCHAR(50)? It is default for all parameters used in the sp [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Did you pass valid values for all parameters? Madhivanan Failing to plan is Planning to fail

in reply to frankalis , yes an empty string madhavinan- yes passed valid vlues for all parameters pomela- @email1 is been fed by a session varaiable(asp)
@email by a form(asp)
thanx all

Old style ASP, not ASP.NET? Then you only have Variant type variables – and you should always double-check values coming from variants. Anyway, it won’t hurt if you use IF ISNULL(@email, ”) <> ” in the procedure to cover nulls. The same for the other parameters as well. If you should try adding defaults to the parameter declaration of the procedure, then remember that defaults only apply if they are not referred to by the calling application. CREATE PROCEDURE dbo.MyTest @1 VARCHAR(10) = ” Call this procedure like so: DECLARE @a VARCHAR(10)
EXEC dbo.MyTest @a … then you will see that, since @a is uninitialized, it has a null value, which is passed on to @1, and as a result @1 will not have its default value. If you call the procedure like this: EXEC dbo.MyTest … then you will see the default kick in.

in general though it would be better to do all of the validation up front and then only do the single update when all is valid. This avoids most of the problems above i.e. something like
ALTER procedure dbo.stp_updateprofile
(
@email1 varchar(50),
@email varchar(50),
@passwd varchar(50),
@nationality varchar(50),
@state varchar(50),
@lga varchar(50),
@street varchar(50),
@addresslga varchar(50),
@phone varchar(50) ,
@sex varchar(50)
)
as declare @error int
set @error = 0 if isnull( @email, ” ) = ” set @error = 1
if isnull( @passwd, ” ) = ” set @error = 1
if isnull( @nationality, ” ) = ” set @error = 1
if isnull( @state, ” ) = ” set @error = 1
if isnull( @lga, ” ) = ” set @error = 1
if isnull( @street, ” ) = ” set @error = 1
if isnull( @addresslga, ” ) = ” set @error = 1
if isnull( @phone, ” ) = ” set @error = 1
if isnull( @sex, ” ) = ” set @error = 1 if @error = 0
update waecpast
set email = @email,
[password] = @passwd,
nationality = @nationality,
state = @state,
lga = @lga,
street = @street,
addresslga = @addresslga,
phone = @phone,
sex = @sex
where email= @email1

twan thanx a mill!!
solved d problem beautifully

]]>