Turn If statement into Case

Question

Post reply

Turn If statement into Case

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
November 20, 2019 at 2:18 am
Go to Answer
#3699066
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 5 years ago by GrassHopper.

DesNorton

SSC-Insane

Points: 24387

November 20, 2019 at 3:44 am

Answer

#3699090

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;

How to post data/code on a forum to get the best help.

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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.

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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: 4473 More actions · Answer 3

GrassHopper

SSCarpal Tunnel

Points: 4473

November 20, 2019 at 12:33 pm

#3699160

That Worked! Thanks Des.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 4

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

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 5

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: 1004356 More actions · Answer 6

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.

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin