we will discuss table valued parameters in SQL Server.
Table Valued Parameter is a new feature introduced in SQL SERVER 2008. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. Prior to SQL SERVER 2008, it is not possible to pass a table variable as a parameter to a stored procedure.
Let us understand how to pass multiple rows to a stored procedure using Table Valued Parameter with an example. We want to insert multiple rows into the following Employees table. At the moment this table does not have any rows.
![employees table](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9ithHueM3Yj1vzd-FffgB1t4YApUxiylEvx53wDgX1uWmb1qOTHaIWcNrrmHPaQOAlctYdgaIUQau_ZQ0gboVzGbotnmZ0IlOy-OZ9KdEdr8h0ZU9VMVEB9vXH0JpyPyBxWD5HmMF0OE/s1600/employees+table.png)
SQL Script to create the Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10)
)
Go
Step 1 : Create User-defined Table Type
CREATE TYPE EmpTableType AS TABLE
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
Go
Step 2 : Use the User-defined Table Type as a parameter in the stored procedure. Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.
CREATE PROCEDURE spInsertEmployees
@EmpTableType EmpTableType READONLY
AS
BEGIN
INSERT INTO Employees
SELECT * FROM @EmpTableType
END
Step 3 : Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure.
DECLARE @EmployeeTableType EmpTableType
INSERT INTO @EmployeeTableType VALUES (1, 'Mark', 'Male')
INSERT INTO @EmployeeTableType VALUES (2, 'Mary', 'Female')
INSERT INTO @EmployeeTableType VALUES (3, 'John', 'Male')
INSERT INTO @EmployeeTableType VALUES (4, 'Sara', 'Female')
INSERT INTO @EmployeeTableType VALUES (5, 'Rob', 'Male')
EXECUTE spInsertEmployees @EmployeeTableType
That's it. Now select the data from Employees table and notice that all the rows of the table variable are inserted into the Employees table.
![table valued parameters example](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjks4QxUGuHej4I1M1P4SuUPjIAuiuZWIi5CmmEuGPekymvmtxaCqP10a5gNEdbSZZXjJdYyLrEuJ348vqwtD7M9_YZ5-YaTmzy-uYBvsrpaQLxeC9rLSnYDYjsaWDa0wHchW6x73cQgyc/s1600/table+valued+parameters+example.png)
In our next post, we will discuss how to pass table as a parameter to the stored procedure from an ADO.NET application
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.