June 17, 2013 at 1:50 pm
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
June 17, 2013 at 1:56 pm
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
June 17, 2013 at 2:43 pm
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
June 17, 2013 at 4:05 pm
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)
June 18, 2013 at 1:36 am
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
June 18, 2013 at 2:01 am
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.
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
June 18, 2013 at 4:09 pm
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.
June 18, 2013 at 5:32 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply