Solved

Query Mulitiple Select/Case

Posted on 2014-12-07
5
150 Views
Last Modified: 2014-12-13
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
0
Comment
Question by:Benki Canoso
5 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 100 total points
ID: 40485766
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
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 100 total points
ID: 40485838
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 40485854
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 40485861
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 40486040
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 35
search for a string in all tables 4 15
Sql query for filter 12 21
Convert char to decimal in a SQL Server View 14 12
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now