Update Statement not working - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update Statement not working

Author

Topic

hjavaher
Starting Member

16 Posts

Posted - 2009-04-16 : 22:52:58

Hi all, Please help me out on this one as I am getting ready to start banging my head against the wall, I have a stored procedure that updates a table. Below i will past the code for you all to take a look at. I know that my condition statement is true for at least one record how ever when i execute it it will return (0 rows affected) If you could please help me out on this one I would really really really appreciate it.

Thank you,
Jason

Stored Procedure:
---------------------------------------------------------------------

ALTER PROCEDURE [dbo].[SPNewUpdateProfile]
@Username as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@Gender as nvarchar(1),
@DateofBirth as smalldatetime,
@City as nvarchar(50),
@StateName as nvarchar(50),
@Zip as nvarchar(50),
@Country as nvarchar(50),
@CanContact as bit,
@CanUpdate as bit,
@AlertLevel as nvarchar(3),
@UserImage as image = NULL,
@Rating as bit,
@Agreed as bit,
@IP as nvarchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM UserProfile WHERE (Username = @Username))
BEGIN
IF @UserImage IS NULL
BEGIN
UPDATE UserProfile
SET FirstName = @FirstName, LastName = @LastName, Gender = @Gender, DateofBirth = @DateofBirth, City = @City,
StateName = @StateName, Zip = @Zip, Country = @Country, CanContact = @CanContact, CanUpdate = @CanUpdate, AlertLevel = @AlertLevel,
Rating = @Rating, Agreed = @Agreed, DateModified = GETDATE()
WHERE Username = @Username
END
ELSE
BEGIN
UPDATE UserProfile
SET FirstName = @FirstName, LastName = @LastName, Gender = @Gender, DateofBirth = @DateofBirth, City = @City,
StateName = @StateName, Zip = @Zip, Country = @Country, CanContact = @CanContact, CanUpdate = @CanUpdate, AlertLevel = @AlertLevel,
UserImage = @UserImage, Rating = @Rating, Agreed = @Agreed, DateModified = GETDATE()
WHERE Username = @Username
END
END
ELSE
BEGIN
INSERT INTO UserProfile
(Username, FirstName, LastName, Gender, DateofBirth, City, StateName, Zip, Country, CanContact, CanUpdate, AlertLevel, UserImage, Rating, Agreed,
DateAdded, DateModified, IP)
VALUES (@Username,@FirstName,@LastName,@Gender,@DateofBirth,@City,@StateName,@Zip,@Country,@CanContact,@CanUpdate,@AlertLevel,@UserImage,@Rating,@Agreed,
GETDATE(), GETDATE(),@IP)
END
END

Thank you in advance for any help
Jason

Kokkula
Starting Member

41 Posts

Posted - 2009-04-17 : 01:26:07

Hello,

Try this code below, if you have a record with the passed username to the stored procedure then you should get the value 1 in the first select.

ALTER PROCEDURE [dbo].[SPNewUpdateProfile]
@Username as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@Gender as nvarchar(1),
@DateofBirth as smalldatetime,
@City as nvarchar(50),
@StateName as nvarchar(50),
@Zip as nvarchar(50),
@Country as nvarchar(50),
@CanContact as bit,
@CanUpdate as bit,
@AlertLevel as nvarchar(3),
@UserImage as image = NULL,
@Rating as bit,
@Agreed as bit,
@IP as nvarchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Count INT

SET @Count = 0

SELECT @Count= COUNT(1)
FROM UserProfile
WHERE Username = @Username

SELECT @Count

IF ISNULL(@Count,0) <> 0
BEGIN
IF @UserImage IS NULL
BEGIN
UPDATE UserProfile
SET FirstName = @FirstName, LastName = @LastName, Gender = @Gender, DateofBirth = @DateofBirth, City = @City,
StateName = @StateName, Zip = @Zip, Country = @Country, CanContact = @CanContact, CanUpdate = @CanUpdate, AlertLevel = @AlertLevel,
Rating = @Rating, Agreed = @Agreed, DateModified = GETDATE()
WHERE Username = @Username
END
ELSE
BEGIN
UPDATE UserProfile
SET FirstName = @FirstName, LastName = @LastName, Gender = @Gender, DateofBirth = @DateofBirth, City = @City,
StateName = @StateName, Zip = @Zip, Country = @Country, CanContact = @CanContact, CanUpdate = @CanUpdate, AlertLevel = @AlertLevel,
UserImage = @UserImage, Rating = @Rating, Agreed = @Agreed, DateModified = GETDATE()
WHERE Username = @Username
END
END
ELSE
BEGIN
INSERT INTO UserProfile
(Username, FirstName, LastName, Gender, DateofBirth, City, StateName, Zip, Country, CanContact, CanUpdate, AlertLevel, UserImage, Rating, Agreed,
DateAdded, DateModified, IP)
VALUES (@Username,@FirstName,@LastName,@Gender,@DateofBirth,@City,@StateName,@Zip,@Country,@CanContact,@CanUpdate,@AlertLevel,@UserImage,@Rating,@Agreed,
GETDATE(), GETDATE(),@IP)
END
END

Hope its clear and helpful...

Thanks,
Pavan

hjavaher
Starting Member

16 Posts

Posted - 2009-04-17 : 03:55:47

Thank you soo Much Pavan, Its working :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:54:25

whats the purpose of inner IF statement? you have same update satement repeated on both conditional blocks inside

hjavaher
Starting Member

16 Posts

Posted - 2009-04-17 : 11:09:31

quote:
Originally posted by visakh16

whats the purpose of inner IF statement? you have same update satement repeated on both conditional blocks inside

They are not exactly the same one sets the user image and one does not :)

hjavaher
Starting Member

16 Posts

Posted - 2009-04-17 : 11:13:53

hey pavan thanks for the fix :) what I don't understand is that what was wrong with the original update statment? Using visual studio I steped through the sp and it did go into the if statments correctly and executed the update statement and when I look at your solution it seems to be the same update statement ? Once again thanks for your help :)

Jason

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 11:27:18

quote:
Originally posted by hjavaher

quote:
Originally posted by visakh16

whats the purpose of inner IF statement? you have same update satement repeated on both conditional blocks inside

They are not exactly the same one sets the user image and one does not :)

ok...actually you dont need the inner if.
you need only this


....
IF ISNULL(@Count,0) <> 0
BEGIN

UPDATE UserProfile
SET FirstName = @FirstName, LastName = @LastName, Gender = @Gender, DateofBirth = @DateofBirth, City = @City, 
StateName = @StateName, Zip = @Zip, Country = @Country, CanContact = @CanContact, CanUpdate = @CanUpdate, AlertLevel = @AlertLevel, 
UserImage = COALESCE(@UserImage,UserImage),Rating = @Rating, Agreed = @Agreed, DateModified = GETDATE()
WHERE Username = @Username

END
ELSE
BEGIN
INSERT INTO UserProfile
(Username, FirstName, LastName, Gender, DateofBirth, City, StateName, Zip, Country, CanContact, CanUpdate, AlertLevel, UserImage, Rating, Agreed, 
DateAdded, DateModified, IP)
VALUES (@Username,@FirstName,@LastName,@Gender,@DateofBirth,@City,@StateName,@Zip,@Country,@CanContact,@CanUpdate,@AlertLevel,@UserImage,@Rating,@Agreed,
GETDATE(), GETDATE(),@IP)
END
END
...

hjavaher
Starting Member

16 Posts

Posted - 2009-04-17 : 14:46:28

Wow visakh16 that is an elegant way of doing it!! :) thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:49:20

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources