Introduction
This article explains how to create a Stored Procedure that will accept a table as a parameter and pass a table value to a Stored Procedure using the SQL language. Creating this type of procedure is the same as a normal Stored Procedure. This type of Stored Procedure is as simple as a normal Stored Procedure.
Getting Started
To create and demonstrate this type of Stored Procedure we will use the following procedure:
  1. Create your own data type (User Defined data type) as table
  2. Use your own data type in the Stored Procedure
  3. Execute the Stored Procedure by passing the table as a parameter
Create your own data type (User Defined datatype) as table
SQL Server User Defined Data Types
SQL Server provides numerous system data types to store dates, character based data, numeric data, and so on. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in a specific format, alpha numeric employee ID's, IP addresses and so on.
Creating a user defined data type is very simple. The following code syntax creates a user defined datatype named mydatatype:
  1. CREATE TYPE datatypename
  2. FROM varchar(11) NOT NULL ;
In the preceding syntax Create is a SQL keyword that indicates to SQL to start create a command. The types keyword defines that the code will create a user defined datatype. Then to tell what system type will be created, here varchar is the system datatype that specifies to create a user defined datatype having a varchar system datatype with 11 length.
But here you need to create a table type data type. The structure of the datatype should be the same as your table that you want insert data into. If your table contains an identity column, then you no need to create a column of you user defined data type. The following structure defines the structure of the table where I want to insert the data.
  1. CREATE TABLE [Students](
  2. [StudentID] [int] IDENTITY(1,1) NOT NULL,
  3. [StudentName] [nvarchar](30) NOT NULL,
  4. [StudentNumber] [varchar](15) NOT NULL,
  5. [StudentClass] [varchar](50) NOT NULL)
  6. )
As I have said previously, your defined table (User Defined data type) should have the same structure expecting a StudentID Column, Hence your user defined data type is like this below.
  1. CREATE TYPE [dbo].[Student] AS TABLE(
  2. [StudentName] [nvarchar](30) NULL,
  3. [StudentNumber] [varchar](100) NULL,
  4. [StudentClass] [varchar](50) NULL
  5. )
In the preceding as I said above, I have not declared the column [StudentID] because it is an identity column.
Use your own data type in Stored Procedure
Now we will create a Stored Procedure that will use the datatype. The following code creates the Stored Procedure with the user's table as parameter.
  1. CREATE PROCEDURE ManageStudent
  2. (@StudentDetails dbo.Student READONLY)
  3. AS
  4. BEGIN
  5. INSERT INTO dbo.Students(StudentName, StudentNumber, StudentClass) SELECT StudentName,StudentNumber,StudentClass FROM @StudentDetails
  6. END
In the preceding code line 2 of the Stored Procedure declares an input parameter named @StudentDetails using the user defined data type Student, the same data type that we created before. Here we have specified READOLY, its mendatory, otherwise SQL will provide the following error:
Error : The table-valued parameter "@Students" must be declared with the READONLY option.
In the code, line 5 retrieves data from the StudentDetails parameter using a select query as we use to retrieve from the table and insert it into the student table.
The preceding showed how to declare a Stored Procedure, the following code describes how to execute the Stored Procedure in SQL. To execute a Stored Procedure, first declare a variable/parameter with your user defined data type, then insert a value into that and execute the Stored Procedure by passing the variable as a parameter. See the following code.
  1. DECLARE @StudentDetails Student
  3. INSERT INTO @StudentDetails(StudentName, StudentNumber, StudentClass)
  4. VALUES ('Kailash','123','FIFT'),('Chandra','234','SIXTH'),('Behera','345','SEVENTH')
  6. EXEC ManageStudent @StudentDetails
In the preceding the first line declared a variable using your data type. Line numbers 3 and 4 inserted data into the variable and line number 6 executed the Stored Procedure by passing a studentDetails.
The preceding are declaring and executing a Stored Procedure using SQL. My next article is Passing DataTable to StoredProcedure as Parameter in C# that explains how to execute the same Stored Procedure using ADO.Net.
Summary
I hope you have learned to create a Stored Procedure that accepts a table as parameter.