Thursday, March 8, 2012

Columns not updating from Stored procedure

Ok, so I've got the following stored procedure:

ALTER PROCEDURE dbo.tbUserPreferences_UpdateOrInsert

(
@.username varchar(50),
@.preferences varchar(300),
@.view_name varchar(300),
@.default_view varchar(10) = 'Y'
)

AS
UPDATE tbUserPreferences SET @.default_view='N' WHERE username=@.username

-- IF NOT EXISTS (
-- SELECT *
-- FROM tbUserPreferences
-- WHERE username=@.username
-- AND view_name=@.view_name
-- )
-- INSERT INTO tbUserPreferences (username, preferences,view_name,default_view) VALUES (@.username,@.preferences,@.view_name,@.default_view)
RETURN

The commented out section works fine, but the UPDATE line does not. I know there are columns that have "username=@.username", but this call is not updating their default_view column.

Please, if anybody knows why, let me in on the secret. Thanks!Try to execute and check result:

UPDATE tbUserPreferences SET @.default_view='N' WHERE username='your sp param'

select @.@.rowcount|||Wow, I'm so silly. And it took me looking at your reply to get it.

The code I ment to try was:

UPDATE tbUserPreferences SET default_view='N' WHERE username=@.username

"default_view" not "@.default_view". Thank you for the reply. Even though I didn't need to test your suggestion, it made me realize my problem. Thanks!|||It's still a good example of why you should error check your code...

No comments:

Post a Comment