Post reply

conditional if statements

  • 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

  • November 19, 2008 at 8:35 am

    #900573

    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

  • November 19, 2008 at 8:41 am

    #900579

    thanks

  • November 19, 2008 at 8:50 am

    #900587

    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

  • November 19, 2008 at 11:36 am

    #900660

    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]

  • November 19, 2008 at 11:50 pm

    #900897

    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".

  • November 20, 2008 at 12:59 am

    #900908

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

    and make necessary changes.

    DECLARE @userid 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 @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 @OldMerchantELSE @NewMerchant END

    WHERE

    [Username] = @OldUsername

    SELECT * FROM [AppUsers]

  • November 20, 2008 at 2:24 am

    #900924

    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

  • November 20, 2008 at 2:27 am

    #900925

    yip. got it to compile.

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

    thanks guys

  • November 20, 2008 at 2:35 am

    #900928

    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

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

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