Turn If statement into Case

  • GrassHopper

    SSCarpal Tunnel

    Points: 4433

    How can I convert this into a Case statement so that the query is not ran every time for each if statement?

    IF

    (SELECT FormType=ltrim(RTRIM(ISNULL(f1,'')+ISNULL(f2,'')+ISNULL(f3,'')))

    FROM [tblTesti]

    WHERE SASREFNBR=1

    )

    LIKE '%National%Promotional%Form%'

    BEGIN

    EXEC [2017 National Promotional Form] @cmd

    END

    IF

    (SELECT FormType=ltrim(RTRIM(ISNULL(f1,'')+ISNULL(f2,'')+ISNULL(f3,'')))

    FROM [tblTesti]

    WHERE SASREFNBR=1

    )

    LIKE '%Circular%Form'

    BEGIN

    EXEC [2017 Circular Form] @cmd

    END

    IF

    (SELECT FormType=ltrim(RTRIM(ISNULL(f1,'')+ISNULL(f2,'')+ISNULL(f3,'')))

    FROM [tblTesti]

    WHERE SASREFNBR=1

    )

    LIKE '%Select%Nutrition%Form%'

    BEGIN

    EXEC [2017 Select Nutrition Form] @cmd

    END

    • This topic was modified 7 months, 2 weeks ago by  GrassHopper.
  • Jeff Moden

    SSC Guru

    Points: 996645

    I have to ask... what are you pasting the code from?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DesNorton

    SSC-Insane

    Points: 23056

    Try this on for size

    DECLARE @FormType varchar(50); -- Adjust data type and size to match [f1] + [f2] + [f3]

    SET @FormType = ISNULL( (SELECT FormType = ltrim(RTRIM(ISNULL(f1,'')+ISNULL(f2,'')+ISNULL(f3,'')))
    FROM [tblTesti]
    WHERE SASREFNBR=1)
    , '');

    IF ( @FormType LIKE '%National%Promotional%Form%' )
    BEGIN
    EXEC [2017 National Promotional Form] @cmd;
    END;

    IF ( @FormType LIKE '%Circular%Form' )
    BEGIN
    EXEC [2017 Circular Form] @cmd;
    END;

    IF ( @FormType LIKE '%Select%Nutrition%Form%' )
    BEGIN
    EXEC [2017 Select Nutrition Form] @cmd;
    END;
  • John Mitchell-245523

    SSC Guru

    Points: 148761

    It's a CASE expression, not a CASE statement. I wouldn't normally be so pedantic, but here the distinction is important.  An expression evaluates to a value, where as a statement does something, such as running a query, executing a stored procedure, creating a table and so on.  It follows that you can't use a CASE expression to run a stored procedure, and therefore you need to use the IF clauses as in your original code or Des's example.

    John

  • GrassHopper

    SSCarpal Tunnel

    Points: 4433

    That Worked! Thanks Des.

  • GrassHopper

    SSCarpal Tunnel

    Points: 4433

    John, thanks for the clarification in the definition of words.  I'm a little loose with my terminology sometimes.

  • GrassHopper

    SSCarpal Tunnel

    Points: 4433

    Jeff Moden wrote:

    I have to ask... what are you pasting the code from?

    Jeff, I copied it from SQL studio to notepad and then here.  I'm not sure why it put the extra spaces....

  • Jeff Moden

    SSC Guru

    Points: 996645

    GrassHopper wrote:

    Jeff Moden wrote:

    I have to ask... what are you pasting the code from?

    Jeff, I copied it from SQL studio to notepad and then here.  I'm not sure why it put the extra spaces....

    Ah... I see why.  You put it between "code"  markers, which was the old way.  You can now copy directly from SSMS (and, I think, VS, but I don't use VS so don't know for sure) into the new tool they have when making a post.  The colors suck but the rest of the formatting comes out great.  If you look at the tool bar at the top of the edit window when you're creating a post, you'll see the tool.

    When you click on that, a new window will open up for you to paste to.  Follow your nose after that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Phil Parkin

    SSC Guru

    Points: 244578

    For info, here is the original code, formatted and pasted directly from VS:

    IF
    (
    SELECT FormType = LTRIM(RTRIM(ISNULL(f1, '') + ISNULL(f2, '') + ISNULL(f3, '')))
    FROM tblTesti
    WHERE SASREFNBR = 1
    ) LIKE '%National%Promotional%Form%'
    BEGIN
    EXEC [2017 National Promotional Form] @cmd;
    END;

    IF
    (
    SELECT FormType = LTRIM(RTRIM(ISNULL(f1, '') + ISNULL(f2, '') + ISNULL(f3, '')))
    FROM tblTesti
    WHERE SASREFNBR = 1
    ) LIKE '%Circular%Form'
    BEGIN
    EXEC [2017 Circular Form] @cmd;
    END;

    IF
    (
    SELECT FormType = LTRIM(RTRIM(ISNULL(f1, '') + ISNULL(f2, '') + ISNULL(f3, '')))
    FROM tblTesti
    WHERE SASREFNBR = 1
    ) LIKE '%Select%Nutrition%Form%'
    BEGIN
    EXEC [2017 Select Nutrition Form] @cmd;
    END;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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