Programming in MS SQL Server 2012


Transact-SQL local variable is an object that helps to store a single data value of a specific data type. Transact-SQL variables are typically used for

01. To count the number of times a loop is performed or to control how may times the loop is performed in conditional statement.

02. To hold a data value to be tested by a control-of-flow statement.

03. To save a data value to be returned by a stored procedure return code or function return value.

Declare Variable

DECLARE statement initializes a Transact-SQL variable by assigning a variable name. The local variable must start with @ symbol and end with data type and data size. Decimal and Float variable must define with precision and scale. The following statements are used to define different types of variables in SQL Server.

-- Sample Statements --


DECLARE @ProductCode int

DECLARE @ProductName char(50)

DECLARE @PraductPrice decimal(5,2)

DECLARE @NetSalary smallmoney

DECLARE @ProductExpiryDate datetime

DECLARE @DataInt sql_variant

To declare more than one local variable, use a comma after the first local variable defined, and then specify the next local variable name and data type.

-- Sample Statements --


DECLARE @ProductCode int, @ProductName char(50), @PraductPrice decimal(5,2)


DECLARE @NetSalary smallmoney, @ProductExpiryDate datetime, @DataInt SQL_Variant

SELECT or SET statement is used to stores variable initial data value followed by = equal operator.

-- Sample Statements --


DECLARE @ProductCode int

SET @ProductCode = 12345

DECLARE @ProductName char(50)

SET @ProductName = 'MS SQL Server'

* * * * *

Email Your Comment To AUTHOR