August 17, 2012 at 10:35 am
Alright Expert,
This should be done via a stored procedure instead but my curriosity was why it is not allowed in a user defined function as the following code would result in syntax error. can someone shed some light?
CREATE FUNCTION TEST_123(@PARAM nvarchar(50))
RETURNS TABLE AS
IF @PARAM='SOME_TABLE1' BEGIN
RETURN(SELECT ID FROM SOME_TABLE1 )
END ELSE BEGIN
RETURN (SELECT ID FROM SOME_TABLE2 )
END
thanks!
August 17, 2012 at 10:38 am
That can be done in a function, but you need a multi-statement table-valued user defined function, not an inline table-valued user defined function. An inline TVF only allows a single SELECT within the function body.
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
August 17, 2012 at 10:45 am
Since you are using dynamic sql, this could also work:
CREATE FUNCTION TEST_123(@PARAM nvarchar(50))
RETURNS TABLE AS
RETURN(
SELECT
ID
FROM
SOME_TABLE1
WHERE
@PARAM = 'SOME_TABLE1'
UNION ALL
SELECT
ID
FROM
SOME_TABLE2
WHERE
@PARAM <> 'SOME_TABLE1'
);
August 17, 2012 at 12:41 pm
Lynn Pettis (8/17/2012)
Since you are using dynamic sql, this could also work:
CREATE FUNCTION TEST_123(@PARAM nvarchar(50))
RETURNS TABLE AS
RETURN(
SELECT
ID
FROM
SOME_TABLE1
WHERE
@PARAM = 'SOME_TABLE1'
UNION ALL
SELECT
ID
FROM
SOME_TABLE2
WHERE
@PARAM <> 'SOME_TABLE1'
);
indeed a nice way to work around. Thanks for quick response!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply