Table-valued Parameters - SQL Server 2008

  • Article

Hi... here is one more article on SQL Server 2008 Enhancements. In past we should have found it a bit difficult to pass numerous parameters to the T-SQL Statements or Functions or Stored Procedures. The approach we used to take is creating a Temporary Table on Demand and insert the values to the Temporary Table and then call the requisite procedure. In SQL Server 2008 the Table-Valued Parameters have been introduced that helps in us eliminating the cumbersome process.

Table Valued Parameters help in the following:
  1. They are Strongly Typed
  2. We can specify the sort order and the unique key
  3. Reduce Round trips to the server
  4. We can have the table structure of different cardinality
  5. It doesn't need or cause the statement to recompile
  6. It doesn't need lock to initially populate the data from the client
  7. Enables us to include complex business logic in a single routine
  8. It provides a very simple programming model.

 

Do you feel interested to know more about this enhancement .. then read on....

Table-valued Parameters are the new parameter types in SQL Server 2008 and it could be declared by declaring the user defined table types. The Table-valued parameters could be used to send multiple rows of data to a T-SQL statement or routine. Table-valued parameters are like the parameter arrays in OLE DB and ODBC but with better flexibility. It could be used to participate in the set-based operations. Permissions for the Table-valued parameters follow SQL Server Object Model so CREATE, GRANT, DENY, ALTER etc. could be used.

These Table-valued parameters can be created and executed from T-SQL or Managed Code.

 

CODE SAMPLE

USE AdventureWorksLT;

GO

--Create Schema

CREATE SCHEMA Production

GO

--Create Table in which the values

--will be inserted

CREATE TABLE [Production].[BillOfMaterials](

                        [BomID] [int] NOT NULL,

                        [ProductID] [int] NOT NULL,

                        [StandardCost] [money] NOT NULL,

 CONSTRAINT [PK__BillOfMaterials__7E37BEF6] PRIMARY KEY CLUSTERED

(

                        [BomID] ASC,

                        [ProductID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

 

GO

--Adding Constraints to the Table

ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD 

CONSTRAINT [FK__BillOfMat__Produ__7F2BE32F] FOREIGN KEY([ProductID])

REFERENCES [SalesLT].[Product] ([ProductID])

GO

--Adding Constraints to the Table

ALTER TABLE [Production].[BillOfMaterials] CHECK

CONSTRAINT [FK__BillOfMat__Produ__7F2BE32F]

GO

--Create a table type.

CREATE TYPE BomType AS TABLE

(

BomID INT NOT NULL,

ProductID INT NOT NULL,

StandardCost INT NOT NULL

)

GO

CREATE PROCEDURE spInsertBOM

@Bom BomType READONLY

AS

SET NOCOUNT ON

INSERT INTO Production.BillOfMaterials

                        (                       BomID,

                                                ProductID,

                                                StandardCost

                        )

SELECT * FROM @Bom;

GO

DECLARE @Bom

AS BomType;

/* Add data to the table variable. */

INSERT INTO @Bom (BomID, ProductID, StandardCost)

SELECT 1, ProductID, StandardCost

FROM SalesLT.Product WHERE

[Color] = 'Silver' AND [Name] LIKE 'Mountain%'

/* Pass the table variable data to a stored procedure. */

EXEC spInsertBOM @Bom;

 

Restrictions
  1. SQL Server will not maintain statistics on columns of table-valued parameters
  2. Table-valued parameters must be passed as input READONLY parameter to the T-SQL Routine and no DML operations are permitted like insert/update/delete on such parameters of the Table-valued type.
  3. Table-valued parameter can't be used as the target of the SELECT INTO or INSERT EXEC statement. A table valued parameter can be in the FROM Clause in the SELECT INTO and INSERT EXEC.