help with t-sql construct - 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 2000 Forums

Transact-SQL (2000)

help with t-sql construct

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-01-09 : 21:04:10

I have the below SPROC but I need to upgrade it. I need to add the following logic in there.

If the ACTIVE column in the current row = 71 then update to 81
otherwise if the ACTIVE column in the current row = 72 then update to 82. These are the only two possibilities right now. I haven't touched SQL in quite awhile I am a bit mixed up on the best way to structure this .. I know I could probably wrap logic around the whole statement but I think a select case on just the last part of the update column would be cleaner? a bit unsure.. just going for best practice here

Thanks very much :)
Mike123

CREATE PROCEDURE dbo.[update_NewUser_Profile1]
(
@userID [int],
@Address [varchar](50),
@City [varchar](25)
)
AS SET NOCOUNT ON

UPDATE [dbo].[tblUserDetails]
SET
[Address] = @Address,
[City] = @City,
[active] = 72

WHERE
( [UserID] = @UserID)

GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-09 : 21:11:48

[1. In ur problem, nothing mentioned about Address, City & User

2. Good for u to refer to some stuff like syntqax of Update & other SQL

3.

Not clear about ur need, but how about the following:

UPDATE [dbo].[tblUserDetails]
SET [Address] = @Address, [City] = @City, [active] = 81
Where [active] = 71

UPDATE [dbo].[tblUserDetails]
SET [Address] = @Address, [City] = @City, [active] = 82
Where [active] = 72

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-09 : 23:32:32

update tblUserDetails
set [active] = case when [active] = 72 then 82
when [active] = 71 then 81
end
,[Address] = @Address, [City] = @City

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources