**** Please Don’t run the below scripts in Production ****

Variables are used to store the values for use in the scripts, procedures etc

Declraing the varibale in sql server

DECLARE @variableName datatype

Ex: DECLARE SETVariable INT

We can declare number of variables in the same statement of declare keyword.

Ex:

DECLARE @SETVariable INT,@SETVariable2 int, @SELECTVariable INT,@SELECTVariable2

After declaring the variable, we are assigning the values to variables using the set and Select statements

Select is used here for 2 purposes in sql server

First assigning the value to variable and next used to select data from a database.

Syntax of set variable

SET @SETVariable = 1

Syntax of Select variable

SELECT @SELECTVariable = 2

Difference between Set and Select

Set Statement

1.Its an ANSI standard for variable assignment

2. We can assign Value to variable at a time

Ex:  SET @SETVariable = 1 ( Valid)

SET @SETVariable = 1,@SETVariable2=2 ( Not Valid)

3. When assigning from a query and the query returns no result, SET will assign a Null  value to the         variable.

use tempdb

go

create table #temp

(

var1 int,

var2 int)

I am not inserting the data in the #temp table

DECLARE @SETVariable INT

set @SETVariable=(select var1 from #temp)

select   @SETVariable

Result: Null

4. When assigning from a query that returns more than one value, SET will fail with an error.

use tempdb

go

create table #temp

(

var1 int,

var2 int)

iam inserting the data and we will see how this statment works

insert #temp

select 1,1

union all

select 2,2

union all

select 3,3

DECLARE @SETVariable INT

set @SETVariable=select var1 from #temp

print @SETVariable

Result:

Incorrect syntax near the keyword ‘select’.

SELECT Statement

1. Non-ANSI standard when assigning variables.

2.  Can assign values to more than one variable at a time.

Ex:

DECLARE  @SELECTVariable INT,@SELECTVariable2 int,@SETVariable2 int

SELECT @SELECTVariable = 2,@SELECTVariable2=3

select @SELECTVariable,@SELECTVariable2

result: we are getting the results here as values are assigned to the @SELECTVariable and @SELECTVariable2

3. When assigning from a query and the query returns no result, SELECT will not make the assignment      and therefore not change the value of the variable.

use tempdb

go

drop table #temp

create table #temp

(

var1 int,

var2 int)

I am not inserting the data in the #temp table

DECLARE @SELECTVariable INT

select @SELECTVariable=2

select @SELECTVariable=var1 from #temp

select @SELECTVariable

as there is no data in the var1 column of #temp, we are getting the what value is there in the @SELECTVariable variable i.e 2

4. When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row.

Iam insering the data in the #temp table and we will see how this works

insert #temp

select 1,1

union all

select 2,2

union all

select 3,3

(3 row(s) affected)

If we run the below query,

DECLARE @SELECTVariable INT

select @SELECTVariable=2

select @SELECTVariable=var1 from #temp

select @SELECTVariable

we will get the last value of the var1 column i.e 3

Please let us know if you have any other  information on this

Thanks for viewing