How to Work with Scalar Variables in Transact-SQL
In this article I am going to explain about how to work with scalar variable in Transact-SQL.
Introduction
Transact-SQL (T-SQL) is Structured Query Language and it is Microsoft's and Sybase's proprietary. Transact-SQL is originally developed by IBM for querying, altering and defining relational database. Transact SQL is also known as T-SQL. T-SQL supports many feature like variables, loops, conditions and exceptions.
Scalar Variables
We use variable to store values and data. DECLARE statement is used to create variable. All the name of variable in T - SQL start with at sign (@). Scalar Variables in T-SQL store only single value.
Syntax for declaring a variable in T-SQL
DECLARE @variable_name data_type
Syntax for Initializing a variable in T-SQL
SET @variable_name = expression
Example
USE vipendra
DECLARE @invomax MONEY,@invomin MONEY
DECLARE @diffper DECIMAL(8,2)
DECLARE @countinvo INT, @mcnvenid INT
SET @mcnvenid = 23
SET @invomax = (SELECT MAX(invoicetotal) FROM dbo.mcninvoices
WHERE vendorid = @mcnvenid)
SELECT @invomin = MIN(invoicetotal),@countinvo = COUNT(*)
FROM invoices WHERE vendorid = @mcnvenid
PRINT 'Maximam invoice of comapny :' + CONVERT(VARCHAR,@invomax) + '.'
PRINT 'Minimum invoice of comapny :' + CONVERT(VARCHAR,@invomin) + '.'
PRINT 'Number of invoice in comapny :' + CONVERT(VARCHAR,@countinvo) + '.'
|
Output