You are here:Home » variable » SET vs SELECT for variable assignment in SQL Server

SET vs SELECT for variable assignment in SQL Server

I experienced in issue with SELECT while used for variable assignment with in the stored procedures. I have captured that moment and had post for the same.

Here i want to give you some demo for the SELECT and SET using with variable assignment. Below are for the same.

Create one table and inserting the values.

CREATE TABLE SampleTable
(
[Id] INT,
[Name] VARCHAR(50)
)
GO

INSERT INTO SampleTable
VALUES(3,'Test3')
GO
...
...

See the table data



#1

DECLARE @Name VARCHAR(50)
SET @Name = (SELECT [NAME] FROM SampleTable WHERE id=1)
SELECT @Name
GO

While running above query raise error as following . To resolve the issue for above error,
we need to use TOP 1


DECLARE @Name VARCHAR(50)
SET @Name = (SELECT TOP 1 [NAME] FROM SampleTable WHERE [Id]=1 
order by [Id] DESC)
SELECT @Name as [Name]
GO

Above query will return the fisrt row for perticular Id
We can also use SELECT clause to get resolve the sane issue
Below is the example using Select clause

DECLARE @Name VARCHAR(50)
SELECT @Name = [NAME] FROM SampleTable WHERE id=1 order by [Id] DESC
SELECT @Name as [Name
GO
Above query will not raise any error and apply last one value from among to variable.

#2

Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Using Select'
GO

We can assign values to multiple variable in single statement
And can not do it with SET, we must assign value to variable using individual statement

Declare @Id int, @Name varchar(50)
SET @Id =2 
SET @Name = 'Using SET'
GO

#3

Variable assignment using SELECT will retain the old value if next statement does not have value.

DELETE FROM SampleTable -- Deleting records from table
Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Old Value'
SELECT @Id = [Id] ,@Name = [Name] FROM SampleTable
SELECT @Id as [NewIdVal], @Name as [NewNameVal]
GO

Result for the above query



Variable assignment using SET will not retain the old value if next statement does not have value , assigne NULL value to varible from next statement even it does not have value.

DELETE FROM SampleTable -- Deleting records from table
Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Old Value'
set @Id = (SELECT TOP 1 [ID] FROM SampleTable)
set @Name = (SELECT TOP 1 [Name] FROM SampleTable)
select @Id as [NewIdVal], @Name as [NewNameVal]
GO

Result for the above query



Lastly i suffered from issues when i have used SELECT for the variable assignment and used that variable  with while loop. So when there are no any values in tables to be assigned for variables. At that time execution was gone infinitely and  never end. After I have used SET and it solved my issue.

Let me share your experience if you ran into issue with SET or SELECT.