Assigning Values of Select Statement to a Variable in SQL

Assigning Value of Select Statement to a Variable in SQL

You often have a requirement like you have to set a  value to a variable to Perform 

some operation based on that value to explain it in a more better way 

I am creating a table

--Create a Table for Demo Purpose

create table Projects
(
ProjectName nvarchar(50),
ResourceName nvarchar(50),
Hours int,
Cost float
)

insert into Projects values('ProjA',1,8,3000),('ProjA',2,7,7000),('ProjA',1,3,3000),('ProjB',4,8,3700),('ProjB',1,5,3800),('Projc',1,5,3800),('ProjD',1,5,10,000)

select * from Projects

Now You want to store value of Project in a variable name @projectname   which matches the condition where cost =10000 to perform operation like updation cost adding 3000 to cost of that project

--use declare to declare a variable next datatype of variable

declare @projectname nvarchar(50)

select @projectname=projectname from projects where cost =10000

select @projectname 

Output ProjD

Now you want to add 3000 to cost to ProjD

declare @projectname nvarchar(50)

select @projectname=projectname from projects-- where cost =10000

select @projectname 

update Projects  set cost =cost+3000 where projectname=@projectname  

select * from projects where projectname=@projectname

ProjectName ResourceName Hours Cost

ProjD                  1             5              13000

In the above code it will take the Project name where cost =10000 

Update value to 13000  of the project assigned in the @projectname variable

Other way of assiging value to variable

declare @projectname nvarchar(50)

set @projectname=(select projectname from projects where cost =10000)

select @projectname 

Output ProjD

If you want to assign two or more value to two variable 

declare @projectname nvarchar(50)

declare @resourcename nvarchar(50)

select @projectname=projectname,@resourcename=resourcename  from projects where cost =13000

select @projectname ,@resourcename

Output 

ProjD ,1

Imp thing to keep in Mind with Variable

1- if the where clause has more than one Rows while assigning value to a variable it will take the value of First Row

Suppose ProjA and ProjD has same cost 13000

then it will take first row value which is ProjA

leaving Second Row  i.e Proj D Values 

2-If you do not specify any where condition to select clause it will Take the Value of Last Row

like 

declare @projectname nvarchar(50)

select @projectname =projectname from projects

select @projectname

Output 

ProjD 

This will take last Row ProjectName  value which  is Project D

NOTE: Keep in mind that while assigning Value to a variable in Select statement you cannot perform Data Retrieval operation means there should be only those column name whose values you are retrieving if you will include other columns in that query it will throw an Error.
To check run this query

declare @projectname nvarchar(50)

select @projectname =projectname,resourcename from projects

Error

Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

The Reason is you have added a ResourceName column in the query which is not assigning value to any variable.