|
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
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
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.
|
|
|
|
|
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).
|
|
|
|
|
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.
|
|
|
|
|
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  .... Can You Explain That... Pls... Any Explanation will be appreciated....
Thanks....
|
|
|
|
|
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).
|
|
|
|
|
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.....
|
|
|
|
|
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).
|
|
|
|
|
Pablo Aliskevicius wrote: <layer>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...
Raj.......
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
