CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Vasil Ivanishvili
Vasil Ivanishvili

Reputation: 47

If Without Else in Procedures

I have Following Procedure:

CREATE PROC spInvTotal2
   @DateVar smalldatetime = NULL
AS
IF @DateVar IS NULL
    SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;

Here we have IF statement but not ELSE. What happens if @DateVar IS NULL or @DateVar IS NOT NULL?

And We have two SELECT Statements. Do they run both?

So if I insert ELSE the meaning of code will be the same?

CREATE PROC spInvTotal2
   @DateVar smalldatetime = NULL
AS
IF @DateVar IS NULL
    SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
ELSE
SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;

Upvotes: 0

Views: 119

Answers (3)

Ilyes
Ilyes

Reputation: 14928

If you don't use begin ... end this query will run anyway

SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= @DateVar;

which mean in both cases (@DateVar IS NULL or @DateVar IS NOT NULL) it will run.

And We have two SELECT Statements. Do they run both?

The first query will run only when the condition is met, and the second query will run in both cases.

Conclution:

  • If @DateVar IS NULL both queries will run.
  • If @DateVar IS NOT NULL only the second query will run.

Since you don't ask for how to solve it (only ask for what happen) I did not give a solution

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81960

Just another option is to use IsNull()

SELECT SUM(InvoiceTotal)
FROM Invoices
WHERE InvoiceDate >= IsNull(@DateVar,( Select  MIN(InvoiceDate) FROM Invoices ) )

Upvotes: 3

user4321
user4321

Reputation: 635

if @DateVar IS NULL ---> the logic below will run

SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;

or @DateVar IS NOT NULL ---> the logic under IF block will be skipped. And rest of the code will run.

So the better practice is to use the if block

like 

Begin
 IF @DateVar IS NULL
    SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
END

Upvotes: 0

Related Questions