Introduction
This article gives an explanation about how to declare variable and set value in SQL server and how to set value in a variable in SQL server as well as set value in the variable in SQL server and how to set variable value in SQL Server stored procedure and how to set a default value for the variable in SQL server and how to set variable value in select statement in SQL server and also shows how to set multiple values to a variable in SQL server and declare variable and set value in SQL query.
In my previous articles, I explained AngularJs Table with Bootstrap 4 in ASP.NET Web Forms And SQL Server STUFF() Function With Syntax and Example And SQL Server Basic Performance Tuning Tips and Tricks And MVC Angular CRUD Operation Using WEB API 2 With Stored Procedure And Export JSON Data to Excel/CSV File using AngularJs With Bootstrap and many others.
Many developers/students those who work with Microsoft SQL Server will have at least heard talk about the variables in SQL Server. Even if any developers/students know on a basic level what is variables in SQL Server, use of variables in SQL Server as well as set values in the local variable and use that value as per need, but still many developers/students didn't know about variables in SQL Server, they didn't know how to use variables, how to set values in variable even they don't know how to declare variables in SQL Server, So in this article I am going to share some basic information regarding variables in SQL.
Requirement
1) Give Simple Definition of the variable in SQL Server
2) Explain the Syntex of Variable Declaration in SQL Server
3) GIve some Example to Store values in the variable.
4) GIve some Example to Store multiple values in the variable.
What is a variable in SQL Server?
In SQL Server, a variable allows a programmer/developers/students to store data temporarily during the execution of code.
Syntex to Declare Variable in SQL Server
DECLARE @Your_variable_name datatype [ = initial_value ], @Your_variable_name datatype [ = initial_value ], ...;
If you analyzed above syntax then @Your_variable_name indicates the name of your variable. and datatype indicates datatype of your variables such as VARCHAR(50), INT, FLOAT, and etc.and [ = initial_value ] indicate default value for your variable.
Example
Let's take a simple example for variable declaration in SQL server.
1) Declare a Single variable with Default Value
DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya'
Here, I have declared variable EmployeeName with datatype VARCHAR(50) and default value 'Nikunj Satasiya'. Now, If I will Print or Select declared variable @EmployeeName then you can see result same as shown below.
PRINT @EmployeeName
--SELECT @EmployeeName AS Employee
Result
Nikunj Satasiya (1 row(s) affected)
2) Declare a Single variable Without Default Value
DECLARE @EmployeeName VARCHAR(50)
Here, I have declared the same variable as per the case 1 @EmployeeName with datatype VARCHAR(50) but I didn't pass any default value. Now, I'll show you how you can set values in the declared variable and get expected result set as output.
-- Variable Declaration DECLARE @EmployeeName VARCHAR(50) -- Set Value in Variable SET @EmployeeName = 'Nikunj Satasiya' -- Print Variable OR SELECT Variable PRINT @EmployeeName SELECT @EmployeeName AS Employee
Result
Nikunj Satasiya (1 row(s) affected)
If, You analyzed the second statement then here I have use SET to assign values in variable EmployeeName. In SQL server SET is used to assign a value to a variable.
3) Declare Multiple variables With Default Value
-- Multiple Variable Declaration With Default Value DECLARE @EmployeeName VARCHAR(50) = 'Nikunj Satasiya', @Company VARCHAR(50)= 'Casepoint LLC.' -- Print OR SELECT Multiple Variables PRINT @EmployeeName PRINT @Company SELECT @EmployeeName AS Employee SELECT @Company AS Company
Result
Nikunj Satasiya Casepoint LLC. (1 row(s) affected) (1 row(s) affected)
Here, I declared variable same as case one but here I declared multiple variables with the same Datatype and different default value.
4) Set Values in variables with Select Statment
Here, I'll show you how you can set the value of the column using select statement into a variable.
Method 1: Set value in Single Variable.
--Set Value in Single Variable With SELECT Query in SQL SEREVR SELECT @EmployeeName = EmployeeName FROM EmployeeName_Master WHERE EmpID = 104
PRINT @EmployeeName
Result
Nikunj Satasiya (1 row(s) affected)
Method 2: Set value in Multiple Variable.
--Set Value in Multiple Variable With SELECT Query in SQL SEREVR SELECT @EmployeeName = EmployeeName, @Company = Company FROM EmployeeName_Master WHERE EmpID = 104
PRINT @EmployeeName PRINT @Company
Result
Nikunj Satasiya Casepoint LLC. (1 row(s) affected) (1 row(s) affected)
If you analyzed method one and method two then @EmployeeName and @Compnay is variable and values of column EmployeeName is stored in EmployeeName variable and values of column Company is stored in the @Compnay variable whose EmpID is 104.
Summary
This article gives an explanation about how to declare the variable and set value in SQL server as well as shows a different kind for methods to stores values in a variable and it's used in SQL server.
If you have any questions/query regarding this then please you can ask your queries I will help you to resolve your queries and issues regarding your SQL server database.
If you have any questions/query regarding this then please you can ask your queries I will help you to resolve your queries and issues regarding your SQL server database.
