Variable and Assignment enhancements

Variable and assignment enhancements are small plus points in SQL Server 2008. We have around 4 things to discuss under this and they are below. All of these are mostly helpful to developers.

• Declare Statement
• Compound Assignment Operators
• Row Constructors
• Table Valued Parameter (TVP) Lets discuss on this titles one by one

Declare statement

• Now you can assign values to the variables at the time of declaration. In previous versions we will need to use SET or SELECT statement to assign the values
• This cant be used not applicable against TEXT, NTEXT or IMAGE data types

DECLARE @variable VARCHAR(30)= 'DECLARE STATEMENT'
PRINT @variable

Compound Assignment Operators

Ability to perform compound assignments in another new and fundamental T-SQL enhancement. In the older version we will be using the variable before and after the equals where as here you can directly use the compound assignment operator.

DECLARE @variable INT= 100
SET @variable+=300
PRINT @variable

You can find the list of compound assignment operators below

Row Constructors

Allows you to insert multiple rows within a single insert statement. Consider you are going to insert 10 rows, in previous versions we need to write 10 INSERT statements, where as here you can use one INSERT statement to accomplish this.

DECLARE @t TABLE (i INT)
INSERT @t VALUES (1),(2),(3)
SELECT * FROM @t

Table Values Parameter (TVP)

• As the name implies it allows you to pass a table as parameter to a stored procedure or function
• Passing table variable as parameters causes SQL Server to materialize the tables in tempdb database

Steps for TVP  
• Create a table type
• Create a table , assign it to the table type and populate the data
• Create a procedure to use the new table

Create a table Type

CREATE TYPE dbo.my_table_type AS TABLE (dbid INT)
GO

Create a table by assigning the table type

--Assigning the table variable to table type
DECLARE @TVP_table_my_type my_table_type
--Populate some data
INSERT INTO @TVP_table_my_type
SELECT database_id FROM sys.databases
--Checking the table variable for data
SELECT * FROM @TVP_table_my_type

Creating a procedure to pass table as parameter

In the previous step Ive populated database id to the table, in this procedure Im just going to retrieve the db name using that table. Since this is for example am creating a simple TVP procedure

CREATE TABLE dbname(id INT, nam VARCHAR(50))
GO
CREATE PROC usp_TVP_proc (@TVP_Param my_table_type READONLY)
AS
    INSERT INTO dbname(id, nam)
    SELECT [dbid],DB_NAME([dbid]) FROM @TVP_Param
GO

Testing the procedure

--Assigning the table variable to table type
DECLARE @TVP_table_my_type my_table_type
--Populate some data
INSERT INTO @TVP_table_my_type
SELECT database_id FROM sys.databases
--Testing the procedure
EXEC usp_TVP_proc @TVP_table_my_type
--Checking the data for populated data
SELECT * FROM dbname

You can see that the table is used in the procedure.