sql query problem on update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query problem on update

Author

Topic

soniyakapoor03
Starting Member

11 Posts

Posted - 2006-03-07 : 10:43:56

hi,

i am creating an sql query for adding and user details.
on updating details i am not passing password.
but an error is always occurring asking me to supply password.
please go thru the query.
it will be helpful.

i don't want to pass password on updating ie my problem.

CREATE procedure AddUserDetails1
(
@UserID int,
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Address varchar(150),
@City varchar(50),
@State varchar(50),
@CountryId int,
@Zip varchar (50),
@Phone varchar(50),
@Fax varchar(50),
@EmailAddress varchar(50),
@DivisionId int,
@RoleId int,
@ReturnValue int output

)
AS

select UserID from users where UserID=@UserID
if @@rowcount>0
Begin
if (@Password)IS NULL
Begin
UPDATE users SET LoginName=@UserName,FirstName=@FirstName,LastName=@LastName,Address=@Address,City=@City,State=@State,Country_Id=@CountryId,ZipCode=@Zip,Phone=@Phone,Fax=@Fax,Email=@EmailAddress,DivisionId=@DivisionId,RoleId=@RoleId
WHERE UserId=@UserId
set @ReturnValue=-3
end
else
Begin
UPDATE users SET LoginName=@UserName,[Password]=@Password,FirstName=@FirstName,LastName=@LastName,Address=@Address,City=@City,State=@State,Country_Id=@CountryId,ZipCode=@Zip,Phone=@Phone,Fax=@Fax,Email=@EmailAddress,DivisionId=@DivisionId,RoleId=@RoleId
WHERE UserId=@UserId
set @ReturnValue=-3
endend
ELse
Begin
SELECT LoginName from users where LoginName=@UserName
if @@rowcount=0
BEGIN
INSERT INTO Users(LoginName,[Password],FirstName,LastName,Address,City,State,ZipCode,Country_Id,
Phone,Fax,Email,DivisionId,RoleId)
VALUES
(@UserName,@Password,@FirstName,@LastName,@Address,@City,@State,@Zip,@CountryId,@Phone,@Fax,@EmailAddress,@DivisionId,@RoleId)
SET @ReturnValue=@@identity
exec InsertRoleIDs @@identity

END
ELSE

BEGIN
SET @ReturnValue=-2
End

End
GO

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-07 : 11:10:13

If I read that correctly, I suspect the SP is trying to do an INSERT, and when you do an INSERT - i.e the @@RowCount = 0:


SELECT LoginName from users where LoginName=@UserName 
if @@rowcount=0

you DON'T check the @Password parameter. I'm guessing the Users table has the Password column designed as NOT NULL (Makes sense).

Add a check for @Password inside the insert section.

Best way to go imho is to debug the SP in Query Analyzer, or perhaps if that isn't possible, do some print 'here I am' lines to prove where the code is going...

BTW - use the advanced post options, and put your code in CODE tags so we can see the indenting

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources