Use if in function

Question

I use this query, but it does not work:

create FUNCTION getUserCreditPayment
(
    @resellerCode int,
    @userCode int,
    @startDate datetime,
    @endDate datetime
)
RETURNS TABLE AS
RETURN(
    WITH Directories AS 
    (
        CASE 
            WHEN (@userCode != 0)  
            THEN 
                (SELECT * 
                 FROM UserCredit 
                 WHERE userCode = @userCode 
                   AND date >= @startDate AND date < @endDate
                UNION ALL 
                SELECT code, date, price* - 1, 
                       userCode, userCodeDes, customerUserName, type 
                FROM UserPayment 
                WHERE userCode = @userCode 
                  AND date >= @startDate AND date < @endDate)
            ELSE
                (SELECT * 
                 FROM UserCredit 
                 WHERE userCode = @userCode 
                   AND date >= @startDate AND date < @endDate
                UNION ALL 
                SELECT code, date, price* -1,
                       userCode, userCodeDes, customerUserName, type 
                FROM UserPayment 
                WHERE date >= @startDate AND date < @endDate)
         END
    )
    SELECT * 
    FROM Directories
)

Naveen Kumar · Accepted Answer · 2016-04-11 10:14:44Z

2

No Need of Case and if Statement, The only Difference in both script is usercode check which can be handled using OR condition

Try With This

create FUNCTION getUserCreditPayment
(
    @resellerCode int,
    @userCode int,
    @startDate datetime,
    @endDate datetime
)
RETURNS TABLE AS
RETURN(
    WITH Directories AS 
    (

         SELECT  * FROM UserCredit where userCode=@userCode and date>=@startDate and date<@endDate
            UNION ALL SELECT code,date,price*-1,userCode,userCodeDes,customerUserName,type from UserPayment 
            where (userCode=@userCode OR @userCode = 0)  and date>=@startDate and date<@endDate


    )
    SELECT  * FROM   Directories
)

answered Apr 11, 2016 at 10:14

Naveen Kumar

1,54110 silver badges12 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Upvote from my side, especially because you are using the inline TVF-syntax (without BEGIN and END)

TheGameiswar · Accepted Answer · 2016-04-11 12:15:23Z

1

if you want to check mutiple conditions and insert ,you should go for Multi table valued function..Here is one example

create FUNCTION getUserCreditPayment
(
   @id int
)
RETURNS --this is the table which you will return,Populate it with same schema
@test table 
(
id int,
name varchar(max)
)
as
Begin
if (@userCode != 0)
begin
insert into @test
SELECT  * FROM UserCredit where userCode=@userCode and date>=@startDate and date<@endDate
            UNION ALL SELECT code,date,price*-1,userCode,userCodeDes,customerUserName,type from UserPayment 
            where userCode=@userCode and date>=@startDate and date<@endDate
end
else 
insert into @test
SELECT  * FROM UserCredit where userCode=@userCode and date>=@startDate and date<@endDate
            UNION ALL SELECT code,date,price*-1,userCode,userCodeDes,customerUserName,type from UserPayment 
            where userCode=@userCode and date>=@startDate and date<@endD

Return
end

edited Apr 11, 2016 at 12:15

answered Apr 11, 2016 at 10:21

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

3 Comments

Gottfried Lesigang Over a year ago

This answer uses the "old" syntax with BEGIN and END and will therefore perform very badly... There are very rare situations where you cannot reach the same with the "new" inlineable (ad-hoc) syntax, which performs much better!

TheGameiswar Over a year ago

Thank you for the tip,updated,could you share some examples as well

Gottfried Lesigang Over a year ago

Hi, sorry, I was not clear enough. I meant the "single-statement-TVF", which is much better performing as the "multi-statement-function". Look at Naveen Kumars answer. There's just RETURNS TABLE AS ... and then follows a "naked" query. No INSERT no @variable no IF-ELSE ...

Collectives™ on Stack Overflow

Use if in function

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related