conditional if statements

Question

Post reply

conditional if statements

sql_prodigy

SSCrazy

Points: 2254
More actions
November 19, 2008 at 8:09 am

#201463

hi
im writing login stored procedures. im having some difficulty with my update user stored procedure. in a nutshell, the stored procedure expects 3 parameters: Username, Classification and Merchant. A user will have 3 edit boxes on the application developer's web page, the stored procedure needs to update whichever value was entered. I think i may be really confusing myself, so i turn to the pro's to help...
please have a look at the following TSQL and advise, will be much appreciated 🙂
can be just copied and pasted, and executed 😀
DECLARE @AppUsers TABLE(Username VARCHAR(20), Classification VARCHAR(20), Merchant VARCHAR(20), UserCreatedDate DATETIME)
DECLARE @OldUsername VARCHAR(50)
DECLARE @NewUsername VARCHAR(50)
DECLARE @OldClassification VARCHAR(50)
DECLARE @NewClassification VARCHAR(50)
DECLARE @OldMerchant VARCHAR(50)
DECLARE @NewMerchant VARCHAR(50)
SET @OldUsername = 'Joe'
SET @NewUsername = 'David'
SET @OldClassification = ''
SET @NewClassification = 'NewClass'
SET @OldMerchant = ''
SET @NewMerchant = 'NewMerchant'
INSERT INTO @AppUsers ([Username],[Classification],[Merchant],[UserCreatedDate])
VALUES ( 'Joe','OldClass','OldMerchant','2008-11-19 16:58:42.734' )
SELECT * FROM @AppUsers
IF EXISTS(SELECT * FROM [AppUsers] WHERE [Username] = @OldUsername AND @OldClassification = '' AND @OldMerchant = '')
BEGIN
UPDATE [AppUsers]
SET [Username] = @NewUsername
WHERE [Username] = @OldUsername
END
ELSE
IF EXISTS(SELECT * FROM [AppUsers] WHERE [Username] = @OldUsername AND @OldClassification = '')
BEGIN
UPDATE [AppUsers]
SET [Username] = @NewUsername, [Merchant] = @NewMerchant
WHERE [Username] = @OldUsername
AND [Classification] = @OldClassification
END
ELSE
IF EXISTS(SELECT * FROM [AppUsers] WHERE [Username] = @OldUsername AND @OldMerchant = '')
BEGIN
UPDATE [AppUsers]
SET [Username] = @NewUsername, [Classification] = @NewClassification
WHERE [Username] = @OldUsername
AND [Classification] = @OldClassification
END
SELECT * FROM @AppUsers

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Have you considered writing it like this...

[font="Courier New"]UPDATE [AppUsers]

SET [Username] = @NewUsername

WHERE [Username] = @OldUsername

AND @OldClassification = ''

AND @OldMerchant = ''

UPDATE [AppUsers]

SET [Username] = @NewUsername, [Merchant] = @NewMerchant

WHERE [Username] = @OldUsername

AND @OldClassification = ''

UPDATE [AppUsers]

SET [Username] = @NewUsername, [Classification] = @NewClassification

WHERE [Username] = @OldUsername

AND @OldMerchant = ''[/font]

If so, then the next trick to try is like this...

UPDATE [AppUsers]

SET [Username] = @NewUsername,

[Classification] = CASE WHEN @OldMerchant = '' THEN @NewClassification ELSE [Classification] END

WHERE [Username] = @OldUsername

--AND @OldMerchant = ''

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql_prodigy SSCrazy Points: 2254 More actions · Answer 2

sql_prodigy

SSCrazy

Points: 2254

November 19, 2008 at 8:41 am

#900579

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome. Does it work now? If so, it would be beneficial to post your completed query here - you never know when someone else is going to come up with a similar problem. This particular problem is not uncommon.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 4

I'm thinking something like this should also work... assuming username is enforced unique.

[font="Courier New"]UPDATE @AppUsers

SET UserName = COALESCE(@NewUserName, UserName),

Classification = COALESCE(@NewClassification,Classification),

Merchant = COALESCE(@NewMerchant,Merchant)

WHERE UserName = @OldUserName[/font]

The quirk of this method is that you can't ever replace an existing Value with a NULL value. This can be handled as well if you need to update something to NULL.

Here's an example of this applied to your setup:

[font="Courier New"]DECLARE @AppUsers TABLE(Username VARCHAR(20), Classification VARCHAR(20), Merchant VARCHAR(20), UserCreatedDate DATETIME)

DECLARE @OldUsername VARCHAR(50)

DECLARE @NewUsername VARCHAR(50)

DECLARE @OldClassification VARCHAR(50)

DECLARE @NewClassification VARCHAR(50)

DECLARE @OldMerchant VARCHAR(50)

DECLARE @NewMerchant VARCHAR(50)

INSERT INTO @AppUsers ([Username],[Classification],[Merchant],[UserCreatedDate])

SELECT 'Joe','OldClass','OldMerchant','2008-11-19 16:58:42.734' UNION ALL

SELECT 'Mike','OldClass2','OldMerchant2','2008-11-19 16:58:42.734' UNION ALL

SELECT 'Steve','OldClass3','OldMerchant3','2008-11-19 16:58:42.734' UNION ALL

SELECT 'Bob','OldClass4','OldMerchant4','2008-11-19 16:58:42.734' UNION ALL

SELECT 'Ted','OldClass5','OldMerchant5','2008-11-19 16:58:42.734'

SELECT * FROM @AppUsers

SET @OldUsername = 'Joe'

SET @NewUsername = 'David'

SET @OldClassification = ''

SET @NewClassification = 'NewClass'

SET @OldMerchant = ''

SET @NewMerchant = 'NewMerchant'

UPDATE @AppUsers

SET UserName = COALESCE(@NewUserName, UserName),

Classification = COALESCE(@NewClassification,Classification),

Merchant = COALESCE(@NewMerchant,Merchant)

WHERE UserName = @OldUserName

SET @OldUsername = 'Steve'

SET @NewUsername = 'NotSteve'

SET @OldClassification = ''

SET @NewClassification = 'NewClass2'

SET @OldMerchant = ''

SET @NewMerchant = NULL --This won't actually do anything.

UPDATE @AppUsers

SET UserName = COALESCE(@NewUserName, UserName),

Classification = COALESCE(@NewClassification,Classification),

Merchant = COALESCE(@NewMerchant,Merchant)

WHERE UserName = @OldUserName

SELECT * FROM @AppUsers [/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

sql_prodigy SSCrazy Points: 2254 More actions · Answer 5

hi Seth, you code does not compile.

Msg 137, Level 15, State 2, Line 28

Must declare the scalar variable "@NewUserName".

Msg 137, Level 15, State 2, Line 41

Must declare the scalar variable "@NewUserName".

sql_prodigy SSCrazy Points: 2254 More actions · Answer 6

found a solution. just declare a table variable with columns, UserID, Merchant and Classification

and make necessary changes.

DECLARE @user-id INT

DECLARE @OldUsername VARCHAR(50)

DECLARE @NewUsername VARCHAR(50)

DECLARE @OldClassification VARCHAR(50)

DECLARE @NewClassification VARCHAR(50)

DECLARE @OldMerchant VARCHAR(50)

DECLARE @NewMerchant VARCHAR(50)

SET @OldUsername = 'ABC'

SET @NewUsername = 'ABCDE'

SET @NewClassification = ''

SET @NewMerchant = ''

SET @user-id =(SELECT [UserID] FROM [AppUsers] WHERE [Username] = @OldUsername)

SET @OldMerchant =(SELECT Merchant FROM [AppUsers] WHERE [Username] = @OldUsername)

SET @OldClassification =(SELECT [Classification] FROM [AppUsers] WHERE [Username] = @OldUsername)

UPDATE

[AppUsers]

SET

[Username] = @NewUsername,

[Classification] =CASE WHEN @NewClassification = ''THEN @OldClassification ELSE @NewClassification END,

Merchant =CASE WHEN @NewMerchant = ''THEN @OldMerchantELSE @NewMerchant END

WHERE

[Username] = @OldUsername

SELECT * FROM [AppUsers]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

yisaaacs (11/19/2008)
hi Seth, you code does not compile.
Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@NewUserName".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar variable "@NewUserName".

yisaaacs, this looks like case-sensitivity.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sql_prodigy SSCrazy Points: 2254 More actions · Answer 8

yip. got it to compile.

but i found a better way, as my previous post.

thanks guys

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

yisaaacs (11/20/2008)
yip. got it to compile.
but i found a better way, as my previous post.
thanks guys

[font="Courier New"]SET @UserID = (SELECT [UserID] FROM [AppUsers] WHERE [Username] = @OldUsername)

SET @OldMerchant = (SELECT Merchant FROM [AppUsers] WHERE [Username] = @OldUsername)

SET @OldClassification =(SELECT [Classification] FROM [AppUsers] WHERE [Username] = @OldUsername)

UPDATE [AppUsers] SET

[Username] = @NewUsername,

[Classification] = CASE WHEN @NewClassification = '' THEN @OldClassification ELSE @NewClassification END,

Merchant = CASE WHEN @NewMerchant = '' THEN @OldMerchant ELSE @NewMerchant END

WHERE [Username] = @OldUsername

-- functionally the same, but three less SELECTs, two less variables.

UPDATE [AppUsers] SET

[Username] = @NewUsername,

[Classification] = CASE WHEN @NewClassification = '' THEN [Classification] ELSE @NewClassification END,

Merchant = CASE WHEN @NewMerchant = '' THEN Merchant ELSE @NewMerchant END

WHERE [Username] = @OldUsername

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden