Similar to other programming languages, SQL also offers a facility to create variables for holding values temporarily. Variables are created in the memory and can be accessed only within the batch in which they have been declared. Here is a simple question on variables;
What are the three types of variables can be created with SQL Server?
- Local variable
- Global variable
- Temporary variable
- Cursor variable
- Table variable
DECLARE @i int; -- this returns NULL SELECT @i; DECLARE @x int = 100; DECLARE @y int, @z int; SET @y = 300; SELECT @z = 400; SELECT @X, @y, @z;
Cursor variables are still given with SQL Server mainly for backward compatibility. Since we can just name to cursor and do all our operations with its name, there is no necessity on declaring a cursor with a variable. Anyway here is an example;
DECLARE @cur CURSOR; DECLARE @i int; SET @cur = CURSOR FOR SELECT ProductID FROM Production.Product WHERE Color = 'Black'; OPEN @cur; FETCH NEXT FROM @cur INTO @i WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @i; FETCH NEXT FROM @cur INTO @i END CLOSE @cur; DEALLOCATE @cur;
Table variables are useful when we need to maintain a small dataset for temporary operations. It works just like local variables and it holds data in the form of a table. Here is an example of it.
DECLARE @Product TABLE ( Id int PRIMARY KEY , Name varchar(100) NOT NULL , Color varchar(100) INDEX ix_Product NONCLUSTERED ); INSERT INTO @Product SELECT ProductID, Name, Color FROM Production.Product WHERE Color IS NOT NULL OR Color != ''; SELECT * FROM @Product;
As you see, three types of variables that can be declared with SQL Server are Local variables, Cursor variables, and Table variables. There are no types called Global and Temporary variables.