Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is variable declared inside IF statement created even when condition evaluates to false?

Since @A is never declared, sql server should throw an error, but it doesn’t. Why is that?

DECLARE @i int = 1;
IF @i > 10
BEGIN
  DECLARE @A int = 100;
END

PRINT @A; // doesn't return any result

thanx

like image 998
AspOnMyNet Avatar asked Jun 01 '10 19:06

AspOnMyNet


People also ask

Can you declare a variable in an if statement?

If you're new to the syntax that's used in the code sample, if (int i = 5) { is a perfectly valid way of declaring and defining a variable, then using it inside the given if statement. It allows us to write terser, clearer code, while also avoiding limiting the scope of a variable.

How declare variable in if condition in C#?

C# has the following conditional statements: Use if to specify a block of code to be executed, if a specified condition is true. Use else to specify a block of code to be executed, if the same condition is false. Use else if to specify a new condition to test, if the first condition is false.


2 Answers

SQL Server does not have block level variable scoping.

It's per batch/stored proc etc

From MSDN (my bold)

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

like image 76
gbn Avatar answered Oct 08 '22 18:10

gbn


gbn's answered the question, but on a related note, watch out for code like this :

DECLARE @i INT = 0
WHILE @i < 2
BEGIN
    DECLARE @a VARCHAR(100)

    IF @i = 0
        SET @a = 'changed'

    PRINT COALESCE(@a, 'is null')   
    SET @i = @i + 1
END

When run, it prints "changed" twice, whereas many people would probably expect it to print "changed", then "is null". Be careful when declaring variables inside loops. You might want to explicitly set it to NULL in the declaration. e.g.

DECLARE @a VARCHAR(100) = NULL

When this modified version is run, it displays "changed" then "is null".

like image 38
Moe Sisko Avatar answered Oct 08 '22 18:10

Moe Sisko