[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Case in Select Statement

Posted on 2012-09-20
6
Medium Priority
?
431 Views
Last Modified: 2012-09-20
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
Comment
Question by:EffinGood
  • 3
  • 3
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38419664
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
 

Author Comment

by:EffinGood
ID: 38419676
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38419699
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:EffinGood
ID: 38419743
Very nice.

What would happen if I had two parameters, such as @pieReason and @pieReason2 I wanted to check for?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38419760
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
 

Author Closing Comment

by:EffinGood
ID: 38420322
awesome
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question