Interesting Observation

virang_21

I was looking at one old SQL stored proc and found this strange behaviour.

DECLARE @Scrap_Qty int
DECLARE @FilledQty int
SET @Scrap_Qty=10 --Change this to 1 and it still run
SET @FilledQty = 2

BEGIN

IF @Scrap_Qty > @FilledQty
declare @currDate varchar(10)
Set @currDate= Convert(VARCHAR(10),GetDate(),103)
PRINT @currDate

END

Look at the if condition .. it does not have BEGIN..END... To make thing interesting weather if condition is true or false you will get the output for parameter @currDate If I add PRINT @Scrap_Qty right after IF and run it again it will only print value for @Scrap_Qty if condition is true. Which leads to question why sql is not complaining about undeclared variable if @Scrap_Qty is less than @FilledQty ? I would have assumed if condition without BEGIN .. END block will behave like a normal one line to execute if condition is true which in above block is declaration of @currDate variable.

DECLARE @Scrap_Qty int
DECLARE @FilledQty int
SET @Scrap_Qty=10 --Change this to 1 and it won't output @Scrap_Qty
SET @FilledQty = 2

BEGIN

IF @Scrap_Qty > @FilledQty
PRINT @Scrap_Qty
declare @currDate varchar(10)
Set @currDate= Convert(VARCHAR(10),GetDate(),103)
PRINT @currDate

END

Zen and the art of software maintenance : rm -rf * Math is like love : a simple idea but it can get complicated.

Pablo Aliskevicius

declare is not an 'executable' statement. This means thet the if in your code relates to the set @currdate.... statement only. Personally, I always use BEGIN...END, even for one liners, but that's a matter of individual preference. Anyway, thanks for sharing.

Pablo. "Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).

agolddog

Pablo Aliskevicius wrote:

Personally, I always use BEGIN...END, even for one liners, but that's a matter of individual preference.

This is exactly the reason for it not to be individual preference. Enhances readability and maintenance. Also, if the original developer used Pablo's notion, they would very likely have encapsulated this right.

Raja Sekhar S

Pablo Aliskevicius wrote:

if in your code relates to the set @currdate.... statement only

In that Case if @ScrapQty<@FilledQty it should not Print Date( as i Understood... If i Understood wrong pls clarify ).... But it's Displaying Date :doh: .... Can You Explain That... Pls... Any Explanation will be appreciated.... Thanks....

Pablo Aliskevicius

The set statement is conditional (first one after the if). The print statement is another, different statement, and not conditional; it will be executed always. The point is: always write begin and end. Best wishes,

Pablo. "Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).

Raja Sekhar S

Pablo Aliskevicius wrote:

The set statement is conditional (first one after the if).

I Understood that but even though the cond'n is false... the variable @currDate is assigned to Current date... That's the thing am unable to understand... Spare my Dumbness.... am trying to learn new things about if.....

Pablo Aliskevicius

I tried this, expecting the first 'print' would be skipped.

if 1 > 2
declare @a int
print 'print #1'
print 'print #2'
select @a = 3
print @a
go
if 2 > 1
declare @b int
print 'print #3'
print 'print #4'
go

To my surprise, the output was this:

print #1
print #2
3
print #3
print #4

The conclusion? ALWAYS write BEGIN and END. Keep code readable.

Pablo. "Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).

Raja Sekhar S

Pablo Aliskevicius wrote:

To my surprise, the output was this:

print #1

That's the thing am Asking.... am Unable to understand why the Print #1 is Printed....... I am trying to Find why it's printing Print #1 with no Luck...... If we Don't use Begin and End( But i always Use....) Then it will execute the first statement... But Here... That's not the case.... Trying to figure out the Mystery Behind it... :doh:

Raj.......