Sql Server: Variable declaration, assignment and precedence
- Posted by Sqltimes
- On April 26, 2014
- 0 Comments
Quick one today:
Starting Sql Server 2008, we have a new way to declare variables and assign values. Earlier we used to use DECLARE statement to declare variables and then in a separate statement, using SET or SELECT statement, assign values to them. Sql Server 2008 combines them to make it easy.
Let’s take an example:
|
1
2
3
4
5
6
7
8
|
---- Declare variables and assign values in seprate statements--DECLARE @Age INT , @Name VARCHAR(30)SET @Age = 32SET @Name = 'SqlTimes' |
Now, we have a new way, that is both simple and intuitive
|
1
2
3
4
5
|
---- New way to declare and assign values--DECLARE @Age INT = 32 , @Name VARCHAR(30) = 'SqlTimes' |
One catch to this is, if you want to assign value of declared variable to another variable, they need to be separate in statements. See below:
|
1
2
3
4
5
6
|
---- Assign one variable value to another--DECLARE @Age INT = 32DECLARE @SomeAge INT = @Age + 20 |
Now, let’s take it a bit further. Let us look at compound assignment. What is the output of the below statements?
|
1
2
3
4
5
6
7
8
|
---- Precedence with compound assignment--DECLARE @Age INT = 5SELECT @Age *= @Age , @Age += @AgeGO |
Let’s break it down. The following is the order in which Sql Server processes the above statement.
- Sql Server, first assigns literal 5 to @Age
- Two, multiplies @Age (5) with @Age (5), resulting in 25
- Three, adds @Age (25) to @Age(25).
- Final result is 50
First operation in SELECT statement is processed first, then goes to the rest of the statements in the order.
Hope this helps,
_Sqltimes
0 Comments