Passing Table to a Store Procedure Parameter in SQL Server 2012

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