Turn If statement into Case

  • GrassHopper

    SSCarpal Tunnel

    Points: 4407

    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 3 weeks, 4 days ago by  GrassHopper.
  • Jeff Moden

    SSC Guru

    Points: 995177

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • DesNorton

    SSC-Insane

    Points: 22855

    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: 148453

    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: 4407

    That Worked! Thanks Des.

  • GrassHopper

    SSCarpal Tunnel

    Points: 4407

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

  • GrassHopper

    SSCarpal Tunnel

    Points: 4407

    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: 995177

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Phil Parkin

    SSC Guru

    Points: 243862

    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.

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

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