Create a User Defined Table Type with the same structure of CricketTeams table
The next step will be to create a User Defined Table Type using the below TSQL Query.
IF EXISTS (
SELECT *
FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N’CricketTeamType’
AND ss.name = N’dbo’)
DROP TYPE [dbo].[CricketTeamType]
GO
/* Create a User Defined Table Type */
CREATE TYPE CricketTeamType AS TABLE
(
CricketTeamID TINYINT,
CricketTeamCountry NVARCHAR (30),
CricketTeamContinent NVARCHAR (50)
)
GO
The newly created User Defined Data Type can be seen by expand the Databases Node | SQL2008TVP | Programmability | Types | User-Defined Table Types. In the below snippet you can see that CricketTeamType Table Type is created. The user defined table types are not deleted when you drop the stored procedure. You need to manually drop the user defined table types when it is not required.
To get more information related to Table Valued Parameters which we have created, you can query the system views such as sys.parameters and sys.table_types.
Create a stored procedure which accepts table type as a input parameter
The next step will be to create a stored procedure which will accept Table Valued Parameter as READONLY input parameter. Procedure can be created by executing the below mentioned TSQL Query.
IF OBJECT_ID (N’dbo.InsertNewCricketTeams’, N’P’) IS NOT NULL
DROP PROCEDURE dbo.InsertNewCricketTeams;
GO
/* Create a procedure which accepts table valued parameter as READONLY input */
CREATE PROCEDURE InsertNewCricketTeams
@InsertNewCricketTeams_TVP CricketTeamType READONLY
AS
INSERT INTO CricketTeams (CricketTeamID,CricketTeamCountry,CricketTeamContinent)
SELECT * FROM @InsertNewCricketTeams_TVP
GO
Declare a variable as Table Type
The next step is to declare a variable as Table Type by executing the below mentioned TSQL Query.
/* Declare a variable that references the table type */
DECLARE @CricketTeamTVP AS CricketTeamType
One important thing to be noted is that you can do anything with the table variable as long as it doesn’t go out of scope. However you cannot modify the table definition.
Populate the Table Type Variable by using an insert statement
The next step will be to populate the Table Type Variable which we have declared earlier by executing the below mentioned TSQL query
/* Populate the table type variable */
INSERT INTO @CricketTeamTVP (CricketTeamID,CricketTeamCountry,CricketTeamContinent)
VALUES
(1, ‘Australia’, ‘Australia’),
(2, ‘India’, ‘Asia’),
(3, ‘Pakistan’, ‘Asia’),
(4, ‘Srilanka’, ‘Asia’),
(5, ‘Bangaladesh’, ‘Asia’),
(6, ‘Hong Kong’, ‘Asia’),
(7, ‘England’, ‘Europe’),
(8, ‘South Africa’, ‘Africa’),
(9, ‘West Indies’, ‘North America’),
(10, ‘Zimbabwe’, ‘Africa’);
Pass the populate table type variable as a READONLY input parameter to the stored procedure
The next step we will be pass the populated Table Type Variable as a READONLY input parameter to the stored procedure which was created earlier.
/* Pass the table type variable to the stored procedure */
EXEC InsertNewCricketTeams @CricketTeamTVP
GO
Check for existence of data
The final step will be to execute the below TSQL Query to see whether the data has got inserted properly into the CricketTeams table.
/* Verify the data which was insered by using the table valued parameter */
SELECT * FROM CricketTeams
GO
One important thing to be noted is that you need to execute the queries within a single batch as shown in the below snippet else you will get out of scope error for the Table Variable.
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@CricketTeamTVP”.
Conclusion
Table Valued Parameters is one of the coolest performance improvements in SQL Server 2008. If you have to insert more than one row of data in a table, in the previous versions of SQL Server you will end up executing stored procedures as many times for every single row. With the introduction of Table Valued parameters you can send multiple rows of data to a single TSQL statement or stored procedures without having multiple round trips between client and the server. However, as Table Variable are created in memory, you should do through analysis that how much memory is required if you use Table Valued Parameter.
]]>