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 
    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)...

Open in new window

Adrian CrossAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Just provide a valid WHERE condition like

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

Open in new window

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

    SELECT *
    FROM dbo.MyTable  
    WHERE  ID = @userID AND Column1 in (a,b,c)
        AND (@userType = 'A' OR Column2 in (d,e,f))
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.
Mark WillsTopic 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
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adrian CrossAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.