November 7, 2013 at 5:59 am
Hello
Is it possible to build an udf that accepts 3 parameters(@a,@b, @C) and, if @a is null (build cte1) else build cte2? I always get an error at the ELSE (incorrect syntax)
EX:
IF (@User_RefID IS NULL)
WITH cte AS (SELECT DISTINCT..[...].)
ELSE
WITH cte AS (SELECT DISTINCT..[...].)
INSERT INTO .....
the function is supposed to return a table
RETURNS @MonthlyReport TABLE ( <columns>>
AS
BEGIN
November 7, 2013 at 6:20 am
IF (@User_RefID IS NULL)
WITH cte1 AS (SELECT ..[...].)
INSERT INTO .....
ELSE
WITH cte2 AS (SELECT ..[...].)
INSERT INTO .....
Watch out for performance from multistatement table-valued user-defined functions. They probably shouldn't be used if they'll return more than a handful of rows.
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
November 7, 2013 at 10:32 am
Thank you very much. I left the insert into statement last and probably that's why I got the error message at ELSE statement. The query will return maximum 15 rows, so I don't think it will be a problem. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply