CODEWITHSUNDEEP
sql-servert-sql
SajjadZare
SajjadZare

Reputation: 2378

Use if in function

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
)

Upvotes: 1

Views: 52

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28910

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

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1541

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
)

Upvotes: 2

Related Questions