Assign value to variable in SQL SELECT Statement

 118 Total Views,  1 views today

You want to retrieve a value from the database and than assign that value to a variable in Sql Server.

For Assign value to variable make sure your query returns at most one row or zero row. One way to be sure is to specify either a primary key or a unique key in the WHERE clause or the table has only one row.

Best practice variable data type and data length size should be same as table column data type and data length size. This technique will avoid error. 

For better understanding we will discuss one problem with example.

Problem: We want to see all information of Customer_id 1002 if the customer city is New York. Please use technique retrieve & assign city value to variable then compare that variable value in your query.

Solution:

First create Customer Table then insert demo data to customer table. 

USE [AdventureWorks]
GO
CREATE TABLE [dbo].[Customer](
Customer_Id int NOT NULL,
Customer_Name varchar(100) NOT NULL,
National_Id_Number varchar(15) NULL,
Date_of_birth datetime NULL,
Marital_Status varchar(15) NULL,
Gender char(1) NULL,
Father_name varchar(50) NULL,
Mother_name char(50) NULL,
Branch_code char(4) not null,
City varchar(50) null,
Country varchar(100)
CONSTRAINT [PK_Customer_Customer_Id] PRIMARY KEY CLUSTERED
(Customer_Id ASC));
GO

insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1001,’Robert Lonson’, ‘010113345’,’01/01/1971′,’Married’,’M’,’Lukako’,’Lumia’,’0001′,’Boston’,’USA’;
GO
insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1002,’William Jonson’, ‘010114345’,’11/19/1982′,’Married’,’M’,’Vittory’,’Hamira’,’0002′,’New York’,’USA’;
GO
insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1003,’Robert Shekh’, ‘010115345’,’10/16/1997′,’Married’,’M’,’Amir Khan’,’Priyanka’,’0002′,’Washington DC’,’USA’;
GO
insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1004,’Jack Kallis’, ‘010116345’,’09/06/1987′,’Married’,’M’,’Son Polok’,’Madhuri’,’0003′,’Boston’,’USA’;
GO
insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1005,’Robindro Jadeja’, ‘010117345’,’08/05/1986′,’Married’,’M’,’Sahid Kapor’,’Kajol’,’0001′,’Washington’,’USA’;
GO
insert into Customer(Customer_Id, Customer_Name, National_Id_Number, Date_of_birth, Marital_Status, Gender, Father_name, Mother_name, Branch_code, City, Country)
select 1006,’Robin Singh’, ‘010118345’,’07/03/1985′,’Married’,’M’,’Souraf’,’Dipika’,’0002′,’New York’,’USA’;
GO

We need to declare @city variable with same data type & data length size as customer table city column. Then we will retrieve & assign customer city to @city variable from customer table for Customer_id 10002. Then we will show the customer_id 1002 details information by comparing @city variable value with “New York” city value if equal.

Final query for how to assign variable to a value in sql select statement:

declare @City varchar(50);
Select @city=city from customer where Customer_Id=1002;

if (@city=’New York’)
begin
select * from customer where Customer_Id=1002;
end;

Here is the output of above query:

how to assign variable to a value in sql select statement
Assign value to a variable in sql select statement

2 thoughts on “Assign value to variable in SQL SELECT Statement”

Leave a Reply

Your email address will not be published. Required fields are marked *