Table Valued Parameters in Sql Server 2008
In short description the Table Valued Parameters are used to pass a Table as a Parameter to a Stored procedure in Sql Server 2008. This helps to avoid comma seperated values to be passed to a parameter.
Overview on Table Valued Parameters in Sql Server 2008
In Earlier version we do not have the option to pass a table to the Stored Procedure(SP). In Sql Server 2008 that option has been introduced to ease the coding.
The first step in achieving this is to create a TYPE, because Sql Server SPs would understand the parameter based on the DataType. There is no DataType Like TABLE in Sql Server SPs. So as to do it we need to Create the TYPE as TableCreating TYPE :
-------------------
Here is the short code to create a TYPE
CREATE TYPE VehicleType AS TABLE
(
Vehicles VARCHAR(100)
)
After you created the TYPE you can write a stored procedure to make use of the TYPECreating Stored Procedure:
------------------------------
Here we are passing the parameter with the TYPE AS Table
CREATE PROCEDURE dbo.VehicleTypeSelection
(
@Vehicles VehicleType READONLY
)
AS
BEGIN
SELECT * FROM @Vehicles
END
After the SP has been created we can call the SP by creating a TABLE of the Type we already CreatedCalling the SP:
-------------------
We can call the above created SP using this code
DECLARE @Vehicles VehicleType
INSERT INTO @Vehicles VALUES('Car')
INSERT INTO @Vehicles VALUES('Bus')
INSERT INTO @Vehicles VALUES('Van')
EXEC dbo.VehicleTypeSelection @Vehicles
In the above Sample code "VehicleType" is the TYPE that we created on the First Step.Conclusion:
---------------
Using this Code sample we can pass the table to a Stored Procedure to avoid temp table and all...
Note: Table Type must be declared as READ ONLY,
We cannot do Datamanipulation on the Table we pass as a parameter to the SP.