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, 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.
  1. CREATE TABLE [dbo].[Student]  
  2. (  
  3.             [StudentID] [intNULL,  
  4.             [StudentName] [varchar](30) NULL,  
  5.             [StudentFees] [intNULL  
  6. )  
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.
  1. CREATE TYPE dbo.StudentType AS TABLE  
  2. (  
  3.    [StudentID] [int] ,  
  4.    [StudentName] [varchar](30) ,  
  5.    [StudentFees] [int]   
  6. )  
Now press F8 to see the created type in the Object Explorer.
 
Database->Programmability->Types->User Define Table Types
 
Type-in-solution-explorer-in-SQL-Server.jpg 
 
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.
  1. CREATE PROCEDURE dbo.StudentDetailProcedure  
  2.    @StudentDetail dbo.StudentType READONLY   
  3. AS  
  4. BEGIN  
  5.    SET NOCOUNT ON  
  6.    INSERT INTO dbo.[Student] ([StudentID], [StudentName], [StudentFees])  
  7.    SELECT [StudentID], [StudentName], [StudentFees] FROM @StudentDetail  
  8. 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:
  1. DECLARE @StudentVariable AS StudentType  
  2. INSERT INTO @StudentVariable([StudentID], [StudentName], [StudentFees]) VALUES(1,'Smith','20000')  
  3. 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.
  1. SELECT  [StudentID]  
  2.       ,[StudentName]  
  3.       ,[StudentFees]  
  4. FROM [master].[dbo].[Student]  
Output
 
Student-table-data-in-SQL-Server.jpg