Can Anyone Explain the Behaviour of this Sql Code......

Question

0.00/5 (No votes)

See more:

DECLARE @Scrap_Qty int
DECLARE @FilledQty int
SET @Scrap_Qty=10 --Change this to 1 and it still Shows Same O/P
SET @FilledQty = 2
 
BEGIN
 
IF @Scrap_Qty > @FilledQty
 declare @currDate varchar(10)   
 Set @currDate= Convert(VARCHAR(10),GetDate(),103)  
 PRINT @currDate
 
END

The Output is:

07/06/2013

in all cases if the cond'n is False also it's Showing the o/p...... I Know there is no Begin and End Statements.... But Unable to understand the Behaviour of this if stmt... Declare is not a Executable stmt.. i know...

Any Explanation Will be helpful.....

(Original Question from The Weird and Wonderful Page posted by Virang_21)
(This Code is not Written by me.. I Won't Ever write This Kind of Code...)

Posted 6-Jun-13 20:54pm

Raja Sekhar S

Updated 6-Jun-13 21:16pm

v4

Add a Solution

3 solutions

Solution 1

I think your BEGIN statement is in the wrong place, so the IF statement only applies to the "declare @currDate varchar(10)", which means the next two statements will always execute. The BEGIN statement should be the first statement after the IF statement.

Regards,
Ian.

Posted 6-Jun-13 21:02pm

Ian A Davidson

Comments

Raja Sekhar S 7-Jun-13 3:09am

Thanks For the reply Ian...

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

Yes i Know that Set @CurDate Stmt Will Execute.. But it will be declared only When the Cond'n is true.... Am thinking that if the cond'n is false Then it has to throw "Error"....?...

Please note: Code not written by me....(i Won't Write this Kind of code..)

Ian A Davidson 7-Jun-13 3:26am

Two possibilities I can think of: either (a) @currDate is already declared somewhere else, or (b) it could be because "DECLARE" is a special statement that must always be included at the START of the procedure; the interpreter is "being nice to you", allowing you to declare it where you have it, but actually declaring it at the start, and then, effectively, ignoring the if statement. Have you tried taking the declare statement out altogether? What happens then?

Raja Sekhar S 7-Jun-13 3:44am

(a) NO @CurDate is not Declared Before...
(b) True... Declare Stmt is not a Executable Stmt as i already mentioned in the Qstn...

The guy who Wrote the Proc have done some Blunder i have to say... or else he don't want anyone to understand his proc...
In this Case the if Stmt executes nothing...... Thanks For the Reply Ian...

Solution 2

You need to put brackets like : IF (@Scrap_Qty > @FilledQty)
in your if statement then it will work

like

  IF (@Scrap_Qty > @FilledQty)
print 'y'

then it will read the first statement

Posted 6-Jun-13 21:06pm

Mahendra.p25

Comments

Raja Sekhar S 7-Jun-13 3:15am

My Friend.... Even though there are no Brackets it will Work...
But my Question is

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

Ian A Davidson 7-Jun-13 3:21am

I don't believe the brackets are needed. http://msdn.microsoft.com/en-us/library/ms182717.aspx

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Accepted Answer · 2013-06-06T21:19:00

Solution 3

Hello,

SQL Server does not have block level variable scoping[^]. And hence your if statement results in executing nothing because the next statement is DECLARE AND is a non-executable statement. The rest two statements execute as usal and therefore you always see the value of curDate getting printed.

Regards,

Posted 6-Jun-13 21:19pm

Prasad Khandekar

Comments

Raja Sekhar S 7-Jun-13 3:49am

Thanks for the reply Prasad...

Your Answer is Helpful.... You got it Right....
But if i use declare and assigning in same stmt then if cond'n works...

 If 1>2
   Declare @A Int=2   --This is Declaring +Assigning so this is Executable Stmt
Print 1
Print @A

The Result is 1
Thanks For the Answer... it Helped...

Prasad Khandekar 7-Jun-13 3:54am

Yes, The assignment statement is an executable statement and hence that assignment will happen in IF.