Passing Table to a Store Procedure in SQL Server

In this article, I described how to pass a table to a Stored Procedure parameter in SQL Server. In this article, you create a student table then create a user-defined table type and pass the table type as a parameter to a Stored Procedure. So let's have a look at a practical example of how to pass a table to a Stored Procedure parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Here is how to implement passing a user-defined table type to a Stored Procedure.

1. Creating a Student Table in SQL Server

Creating a table named Student.

CREATE TABLE [dbo].[Student]  
(  
    [StudentID] [int] NULL,  
    [StudentName] [varchar](30) NULL,  
    [StudentFees] [int] NULL  
)

2. Creating a User-Defined Table Type in SQL Server

Now to create a user-defined table type which will be used as a table valued parameter in the Stored Procedure.  

CREATE TYPE dbo.StudentType AS TABLE  
(  
   [StudentID] [int] ,  
   [StudentName] [varchar](30) ,  
   [StudentFees] [int]   
)

Now press F8 to see the created type in the Object Explorer.

Database->Programmability->Types->User Define Table Types

3. Creating a Stored Procedure in SQL Server

Now create the StudentDetailProcedure Stored Procedure. This Stored Procedure will accept a table valued parameter.

READONLY keyword - This keyword is required to declare a table valued parameter.

CREATE PROCEDURE dbo.StudentDetailProcedure  
   @StudentDetail dbo.StudentType READONLY   
AS  
BEGIN  
   SET NOCOUNT ON  
   INSERT INTO dbo.[Student] ([StudentID], [StudentName], [StudentFees])  
   SELECT [StudentID], [StudentName], [StudentFees] FROM @StudentDetail  
END

4. Using an Exec Command to Execute the Stored Procedure

Now you can declare a variable @StudentVariable containing the value of the table columns. See:

DECLARE @StudentVariable AS StudentType  
INSERT INTO @StudentVariable([StudentID], [StudentName], [StudentFees]) VALUES(1,'Smith','20000')  
EXEC  dbo.StudentDetailProcedure @StudentVariable

5. Using Select Query to display Employee Table Data

Now use a select query to display the above inserted data using an exec command in the Employee Table.

SELECT  [StudentID]  
      ,[StudentName]  
      ,[StudentFees]  
FROM [master].[dbo].[Student]

Output