SQL Server user-defined-function using WITH, IF together

Question

I need to create a scalar-valued user defined function in SQL Server. I need to have with clause to store some intermediary tables which will produce the final return result. I also need IF .. ELSE because depending on the input parameter the query to the resutl is different. However, I can't write the code without error combinaing these two elements together. My function would be like this:

             CREATE FUNCTION [dbo].[getMyValue](
               @inputType int      
               )
            RETURNS float
        AS
        BEGIN       

        DECLARE @result float
        ;

         WITH tempTable AS
            (
                SELECT * from TableA          
            )
              ;

           IF inputType = 1
           set @result = select sum(t.result1) from tempTable
           else 
           selecset @result = select sum(t.result2) from tempTable   
           return @result
        END
        GO

But now it complains incorrect syntax near 'if'. If I remove the with clause (and query against some actual table) it compiles, or if I remove IF statements it also compiles. So how can I make them work together?

Giorgos Betsos · Accepted Answer · 2018-05-17 07:27:24Z

1

You cannot use IF like this in the context of an SQL query. Try using the following instead:

DECLARE @result float, @result1 float, @result2 float

WITH tempTable AS
(
   SELECT * from TableA          
)
SELECT @result1 = sum(case when @inputType = 1 then t.result1 else 0 end), 
       @result2 = sum(case when @inputType = 2 then t.result2 else 0 end)
FROM tempTable

IF @inputType = 1
   SET @result = @result1
ELSE 
   SET @result = @result2

RETURN @result

edited May 17, 2018 at 7:27

answered May 17, 2018 at 7:02

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tete Over a year ago

Thank you for your help! I saw in some other posts saying "can't use IF in a query" but it never occured to me that it applies to me. (I thought my usage of if is for flow control not INSIDE query). Anyway, it works now.

Collectives™ on Stack Overflow

SQL Server user-defined-function using WITH, IF together

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related