if else control flow help - 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)

if else control flow help

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-09 : 14:23:07

Can somebody please help me with this SP, I have been having a bitch of a time getting it right. Basically I just need to dynamically update the "ACTIVE" column based on what its current value is. If it is 9 update it to 2, if it is 8 then update it to 1. I thought it would be better to encapsulate this logic in the SP rather than in my ASP.

Thank you.
Mike

CREATE PROCEDURE [update_verifyEmail]
(
@UserID [int],
@NameOnline [varchar](15),
)

AS
--pseudo code

IF (SELECT active from tbluserdetails where userid = @userID and nameonline = @nameOnline) = '9'

UPDATE [tblUserDetails] SET [Active] = '2'
WHERE ([UserID] = @UserID AND [nameOnline] = @nameOnline )

ELSE

IF (SELECT active from tbluserdetails where userid = @userID and nameonline = @nameOnline) = '8'

UPDATE [tblUserDetails] SET [Active] = '1'
WHERE ([UserID] = @UserID AND [nameOnline] = @nameOnline )

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-09 : 14:36:22

CREATE PROCEDURE [update_verifyEmail]
( @UserID [int], @NameOnline [varchar](15), )
AS
UPDATE [tblUserDetails]
SET [Active] = CASE Active WHEN '9' THEN '2'
WHEN '8' THEN '1' END
WHERE ([UserID] = @UserID AND [nameOnline] = @nameOnline ) AND Active IN ('9', '8')

Edited by - robvolk on 07/09/2002 14:37:06

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-09 : 14:36:25


CREATE PROCEDURE [update_verifyEmail] 
( 
@UserID [int], 
@NameOnline [varchar](15), 
) 

AS 
UPDATE [tblUserDetails]
SET [Active] = case active when '9' then '2'
                           when '8' then '1'
                           else [Active]
               end
WHERE ([UserID] = @UserID AND [nameOnline] = @nameOnline ) 
GO

<edit>I'm hit....If you have triggers on your table for update, I'd used Rob's version.....</edit>
<O>

Edited by - Page47 on 07/09/2002 14:56:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources