CODEWITHSUNDEEP
sql-serverif-statementcase
Alex Theimer
Alex Theimer

Reputation: 43

SQL Nested IF-ELSE Statements

Alright, I'm officially at a loss. I'm trying to create a procedure that runs through each subsequent check and executes the corresponding query.

However, when I execute the procedure with one/two/all arguments, it always gets caught in the final ELSE and spits out the "cover-all" result set. WHY???

I suspect it may be: -If/Else syntax -Use of parentheses -Begin/End keywords -Too many IF conditions?

Any help is extremely appreciated!

Bonus points: can I optimize this with a searched CASE in the WHERE clause? I know I can really shave down this code--but I'm really curious as to why this isn't working.

CREATE PROC spBalanceRange
    @VendorVar varchar(50) = NULL,
    @BalanceMax money = NULL,
    @BalanceMin money = NULL

AS
    IF
    (
        @VendorVar != NULL
        AND @BalanceMin != NULL             
        AND @BalanceMax != NULL
    )

    BEGIN
        SELECT VendorName, InvoiceNumber, InvoiceTotal - (PaymentTotal + CreditTotal) AS BalanceDue
        FROM Invoices JOIN Vendors
            ON Invoices.VendorID = Vendors.VendorID
        WHERE
            VendorName = @VendorVar
            AND InvoiceTotal - (PaymentTotal + CreditTotal) >= @BalanceMin
            AND InvoiceTotal - (PaymentTotal + CreditTotal) <= @BalanceMax
    END
ELSE IF
    (
        @VendorVar != NULL
        AND @BalanceMin = NULL
        AND
            (
                @BalanceMax = NULL
                OR @BalanceMax = 0
            )
    )
    BEGIN
        SELECT VendorName, InvoiceNumber, InvoiceTotal - (PaymentTotal + CreditTotal) AS BalanceDue
        FROM Invoices JOIN Vendors
            ON Invoices.VendorID = Vendors.VendorID
        WHERE
            VendorName = @VendorVar
            AND InvoiceTotal - (PaymentTotal + CreditTotal) > 0
    END
ELSE
    BEGIN
        SELECT VendorName, InvoiceNumber, InvoiceTotal - (PaymentTotal + CreditTotal) AS BalanceDue
        FROM Invoices JOIN Vendors
            ON Invoices.VendorID = Vendors.VendorID
        WHERE
            InvoiceTotal - (PaymentTotal + CreditTotal) > 0
    END
;

Upvotes: 4

Views: 667

Answers (4)

BasicIsaac
BasicIsaac

Reputation: 187

Another approach using a case statement that may show you the results you want:

CREATE PROCEDURE spBalanceRange

@VendorVar varchar(50) = NULL,
@BalanceMax money = NULL,
@BalanceMin money = NULL

AS
BEGIN
SELECT 

b.VendorName, a.InvoiceNumber, 

case 
when InvoiceTotal - (PaymentTotal + CreditTotal) between @BalanceMax and     @BalanceMin
then InvoiceTotal - (PaymentTotal + CreditTotal) 
else 0
end BalanceDue
FROM Invoices a
JOIN Vendors b
ON a.VendorID = b.VendorID
WHERE
b.VendorName = @VendorVar 
and InvoiceTotal - (PaymentTotal + CreditTotal) between @BalanceMax and @BalanceMin
END

Hope I understood what you're trying to accomplish correctly.

Upvotes: 1

user4650542
user4650542

Reputation:

I'm not near a windows machine so this might need tweaking:

CREATE PROC spBalanceRange
    @VendorVar varchar(50) = NULL,
    @BalanceMax money = NULL,
    @BalanceMin money = NULL

AS
SELECT VendorName, InvoiceNumber, InvoiceTotal - (PaymentTotal + CreditTotal) AS BalanceDue
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE
case when @VendorVar is null then true else vendorName=@vendorVar end
AND InvoiceTotal - (PaymentTotal + CreditTotal) >= coalesce(@BalanceMin,0)
AND case when @BalanceMax is null then true else InvoiceTotal - (PaymentTotal + CreditTotal) <= coalesce(@BalanceMax, 1e15) end;

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

Ideally you should use dynamic sql to avoid parameter sniffing like issues in a query like this, a solution with dynamic sql would look something like this..... 

CREATE PROC spBalanceRange
    @VendorVar varchar(50) = NULL,
    @BalanceMax money = NULL,
    @BalanceMin money = NULL

AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);



SET @Sql  = N'SELECT VendorName, InvoiceNumber, InvoiceTotal - (PaymentTotal + CreditTotal) AS BalanceDue
        FROM Invoices 
        INNER JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
        WHERE '
        + CASE 
        WHEN         (@VendorVar IS NOT NULL 
                      AND @BalanceMin IS NOT NULL             
                      AND @BalanceMax IS NOT NULL)
         THEN N' VendorName = @VendorVar
            AND InvoiceTotal - (PaymentTotal + CreditTotal) >= @BalanceMin
            AND InvoiceTotal - (PaymentTotal + CreditTotal) <= @BalanceMax' 

        WHEN          ( @VendorVar IS NOT NULL
                        AND @BalanceMin IS NULL
                        AND (@BalanceMax IS NULL OR @BalanceMax = 0))
         THEN N' VendorName = @VendorVar
               AND InvoiceTotal - (PaymentTotal + CreditTotal) > 0'
        ELSE N' InvoiceTotal - (PaymentTotal + CreditTotal) > 0' END

Exec sp_executesql @Sql 
                  ,N'@VendorVar varchar(50),@BalanceMax money,@BalanceMin money'
                  ,@VendorVar 
                  ,@BalanceMax 
                  ,@BalanceMin 

END

Upvotes: 4

user156213
user156213

Reputation: 766

I think it's because you're comparing them to null with ==. With t-sql you always need to use the is operator.

Try

x is not null

and

x is null

instead.

Upvotes: 1

Related Questions