If/Then in table valued functions

Question

If/Then in table valued functions

Chrissy321

SSCoach

Points: 15769
More actions
June 17, 2013 at 1:50 pm

#276353

Hello All,
Can I use conditional logic in a function that returns a table?
CREATE FUNCTION dbo.TestFunction (@param1 int)
RETURNS
TABLE
AS
RETURN
SELECT
1 as column1,
2 as column2
This works
SELECT * FROM dbo.TestFunction(1)
Really I would like to do something like the below but I am getting the error
Incorrect syntax near the keyword 'IF'.
IF @param1 = 1
BEGIN
SELECT
1 as column1,
2 as column2
END
ELSE
BEGIN
SELECT
3 as column1,
4 as column2
END

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not in an in-line table valued function. If you want anything other than a single select statement, you need a multi-statement table-valued function, though be very careful as they don't perform well when there are lots of rows involved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

can you return a case statement instead?

SELECT

CASE WHEN @param1 = 1 THEN 1 ELSE THEN 3 END as column1,

CASE WHEN @param1 = 2 THEN 2 ELSE THEN 4 END as column2,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chrissy321 SSCoach Points: 15769 More actions · Answer 3

I actually need to pass multiple parameters so CASE logic would probably get really dense. I am going to look at a multi-statement table-valued function.

I will be joining multiple tables but the record sets are fairly small (currently less than 100 and probably less than 200 anticipating growth) so performance shouldn't be an issue.

Thanks for your responses.

Working example of what I need to do below:

CREATE FUNCTION dbo.TestFunction

(

@param1 int

)

RETURNS @test-2 TABLE(column1 int,column2 int)

AS

BEGIN

IF @param1 = 1

BEGIN

INSERT INTO @test-2

SELECT

1 as column1,

2 as column2

END

ELSE

BEGIN

INSERT INTO @test-2

SELECT

3 as column1,

4 as column2

END

RETURN

END

GO

--SELECT * FROM dbo.TestFunction(1)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Chrissy321 (6/17/2013)
I will be joining multiple tables but the record sets are fairly small (currently less than 100 and probably less than 200 anticipating growth) so performance shouldn't be an issue.

To be honest, that's above the max rows I'd recommend in a table variable (which a multi-statement table-valued function uses). Test carefully, consider using something else unless all you're doing is SELECT * FROM fn_MyFunction()

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Chrissy321 (6/17/2013)
I actually need to pass multiple parameters so CASE logic would probably get really dense. I am going to look at a multi-statement table-valued function.
I will be joining multiple tables but the record sets are fairly small (currently less than 100 and probably less than 200 anticipating growth) so performance shouldn't be an issue.
Thanks for your responses.
Working example of what I need to do below:
CREATE FUNCTION dbo.TestFunction
(
@param1 int
)
RETURNS @test-2 TABLE(column1 int,column2 int)
AS
BEGIN
IF @param1 = 1
BEGIN
INSERT INTO @test-2
SELECT
1 as column1,
2 as column2
END
ELSE
BEGIN
INSERT INTO @test-2
SELECT
3 as column1,
4 as column2
END
RETURN
END
GO
--SELECT * FROM dbo.TestFunction(1)

Lowell's suggestion would work fine for this. Can you post up a more realistic version of your query? There are ways to simplify complex conditional logic. Use a tvf if you can.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chrissy321 SSCoach Points: 15769 More actions · Answer 6

Lowell's suggestion would work fine for this. Can you post up a more realistic version of your query? There are ways to simplify complex conditional logic. Use a tvf if you can.

I have 9 parameters being passed. Within the function I have two major if/else statements each of which has two embedded if if/else statements so it would be probably be complicated.

I am going to stick with this logic since its battle-tested. I am moving the logic from a procedure to the function.

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Here's one idea, but I have no idea if it will fit your needs or if it will perform fine

WITH option1 AS(

SELECT 1 AS column1,

2 AS column2

FROM Sometable

WHERE @option = 1

),

option2 AS(

SELECT 1 AS column1,

2 AS column2

FROM Sometable

WHERE @option = 2

)

SELECT column1,

column2

FROM option1

UNION ALL

SELECT column1,

column2

FROM option2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2