UDF - IF(@parameter)

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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