Query Mulitiple Select/Case

I want to create a query as follows. I need help setting put case and select correctly. The below query is more pseudocode than an actual working query.
declare variable @response
set @response = 'User will enter a value'

Case
         when @response = 'Standard' THEN
                           Select NameFirst, NameLast,Status
                            From Members
         when @response = 'Extended' THEN
                          Select NameFirst, NameLast,Status,DateJoin,Comments
        when @response = 'Restricted'
                         Select NameFirst, NameLast,Status,DateJoin,Comments,Address,Phone,Email
          END
Benki CanosoAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Having different columns in the output is not a good idea. It violates the Law of Demeter and the Principle of Least Astonishment. I would not expect a single entity to behave differently on this level, when fed with different input.

This clearly belongs to the front-end. Create three views for each case and use proper permission management.
0
 
SimonCommented:
How do you want to use the result? Is it for dynamic SQL in an EXEC statement? Before using dynamic SQL, make sure you're aware of the risks with it.

declare @response varchar(50)='restricted'
declare @sSQL nvarchar(max)

set @sSQL =(select Case
         when @response = 'Standard' THEN
                           'Select NameFirst, NameLast,Status From ' + quotename('members')
         when @response = 'Extended' THEN
                          'Select NameFirst, NameLast,Status,DateJoin,Comments From ' + quotename('members')
        when @response = 'Restricted' THEN
                         'Select NameFirst, NameLast,Status,DateJoin,Comments,Address,Phone,Email From ' + quotename('members')
          END as col1)

EXECUTE  sp_executesql @sSQL;

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CASE can't conditionally include or not include columns.
So, using IF, a straight answer to the question is..
IF @response = 'Standard'
   begin
   Select NameFirst, NameLast,Status
   From Members
   end
  
IF @response = 'Extended' 
   begin
   Select NameFirst, NameLast,Status,DateJoin,Comments
   From ???
   end
   
IF @response = 'Restricted'
   begin
   Select NameFirst, NameLast,Status,DateJoin,Comments,Address,Phone,Email
   From ???
   end

Open in new window

btw, and (although I take it ste5an already covered this), there may be an issue that whatever downstream processes might not be able to handle a source that can change.  For example, SSIS and SSRS require a 'contract' between source and destination such that if the source changes, it could break the transform which would require a re-mapping.

So ... give us some context as to why you are asking this question.

btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, but it doens't cover this specific issue.  So I'm curious as to why you are asking this, as I have an update to this article coming, and might include your issue.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What you could do however, since the three sets are subsets of each other, is to pass NULLs for the columns you're not using.
IF @response = 'Standard'
   begin
   Select NameFirst, NameLast,Status, NULL as DateJoin, 
   NULL as Comments, NULL as Address, NULL as Phone, NULL as email
   From Members
   end
  
IF @response = 'Extended' 
   begin
   Select NameFirst, NameLast,Status,DateJoin,Comments, 
   NULL as Address, NULL as Phone, NULL as email
   From ???
   end
   
IF @response = 'Restricted'
   begin
   Select NameFirst, NameLast,Status,DateJoin,Comments,Address,Phone,Email
   From ???
   end
   

Open in new window

0
 
Dale FyeCommented:
Another option, which would negate the problem of a query that returns a different number of columns would be something like:

SELECT NameFirst
, NameLast
, Status
, DateJoin = CASE WHEN @response = 'Standard' THEN NULL
                                ELSE DateJoin END
, Comments = CASE WHEN @response IN('Extended', 'Restricted') THEN NULL
                                ELSE Comments END
, Address = CASE WHEN @Response = 'Restricted' THEN Address
                               ELSE NULL END
, Phone = CASE WHEN @Response = 'Restricted' THEN Phone
                               ELSE NULL END
, Email = CASE WHEN @Response = 'Restricted' Then Email
                              ELSE NULL END
FROM yourTable
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.

All Courses

From novice to tech pro — start learning today.