Table-valued function - Return different query table based on condition

Hi, how can I return a select query based on a condition using CASE in a table-valued function?

This is what I'm after,  a select with all its columns. This example would only allow me to return one column.

Create FUNCTION MyFunction
(	@userID	      int,
        @userType	      tinyint 
)
RETURNS TABLE
AS 
RETURN 
(
 SELECT 
    CASE @userType 
           WHEN 'A' THEN (SELECT * FROM MyTable  WHERE  ID = @userID AND Column1 in (a,b,c))
	    WHEN 'B' THEN (SELECT * FROM MyTable  WHERE  ID = @userID AND Column1 in (a,b,c) AND Column2 in (d,e,f)...
	END
)

Open in new window


Thanks!
Adrian CrossAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, it seems to be the same query with some additional requirements for usertype B

first part is the same ie
SELECT * FROM MyTable  WHERE  ID = @userID

Open in new window

the additional bit can be expressed as
 AND ( @usertype = 'A' and Column1 in (a,b,c)
    OR @usertype = 'B' and column1 in (a,b,c) and column2 in (d,e,f)

Open in new window


So, putting it together
SELECT * FROM MyTable  
WHERE  ID = @userID
AND ( @usertype = 'A' and Column1 in (a,b,c)
    OR @usertype = 'B' and column1 in (a,b,c) and column2 in (d,e,f))

Open in new window

0
 
ste5anSenior DeveloperCommented:
Just provide a valid WHERE condition like

SELECT  *
FROM    MyTable
WHERE   ID = @userID
    AND Column1 IN (a,b,c)
    AND (
        @userType = 'B' AND Column2 IN (d,e,f)
        OR
        NOT @userType = 'B'
    )

Open in new window

0
 
Scott PletcherSenior DBACommented:
Based on what you specified, this should do it:

RETURN (
    SELECT *
    FROM dbo.MyTable  
    WHERE  ID = @userID AND Column1 in (a,b,c)
        AND (@userType = 'A' OR Column2 in (d,e,f))
)
0
 
ste5anSenior DeveloperCommented:
Caveat: Using (@userType = 'A' OR Column2 in (d,e,f)) can lead to problems, cause you cannot control the value of @userType. It can be 'C' or even NULL.

So depending on your use-case you need an explicit test and maybe an fallback.
0
 
Adrian CrossAuthor Commented:
Thanks!
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.