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.
  • 2842

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

Clipboard02.jpg


© 2019 DotNetHeaven. All rights reserved.