• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Case in Select Statement

Hi Experts!

I have the following sql I need to modify:

select pieID, pieMakerID from tblPies where activePie = 1;

I need to include a parameter called "@pieReason". If @pieReason = 1 then I need to include the column pieReason aliased as 'Reason for Pie'. If @pieReason equals anything else, I do not want to include it in my select statement at all.

How can I do this?


Thank you!
0
EffinGood
Asked:
EffinGood
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
You should be doing this with an IF statement
If @pieReason = 1  
 select pieID, pieMakerID, PieReason from tblPies where activePie = 1;
ELSE
 select pieID, pieMakerID from tblPies where activePie = 1;


With the case statement, it will always return PieReason to the application

 select pieID, pieMakerID, CASE WHEN @pieReason = 1 PieReason ELSE '' END as Piereason from tblPies where activePie = 1;
0
 
EffinGoodAuthor Commented:
Hi Aneesh,

Thank you for your quick reply! The only thing is, is that my select statement is a lot longer than shown in this example. I'm afraid it would be confusing to developers who look at my sql in the future to see two sets of selects.....any other ideas?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I prefer the IF approach, another option is to use dynamic sql

If @pieReason = 1  
  SET @sql = ' select pieID, pieMakerID' +' , PieReason'
ELSE
    SET @sql = select pieID, pieMakerID '

SET @sql = @sql + 'from tblPies where activePie = 1; '

EXEC(@sql)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
EffinGoodAuthor Commented:
Very nice.

What would happen if I had two parameters, such as @pieReason and @pieReason2 I wanted to check for?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can do the same

If @pieReason = 1  
  SET @sql = ' select pieID, pieMakerID' +' , PieReason'
ELSE
    SET @sql = select pieID, pieMakerID '

If @pieReason1 = 1  
 SET @sql = @sql + ',AnotherColumn'

SET @sql = @sql + 'from tblPies where activePie = 1; '

EXEC(@sql)
0
 
EffinGoodAuthor Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now