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
...
...
Let's 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.
(Click on image to enlarge)
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 first row for particular 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 , assign NULL value to variable 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
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.