Create Stored Procedure with Table Parameter

Kailash Chandra Behera | Sunday, July 24, 2016

Introduction

This article, describes how to create stored procedure with table parameter that will accept a table as a parameter and pass a table value to a Stored Procedure in the SQL Server. Creating this type of procedure is the same as a normal Stored Procedure, but little effort required to create tis type of Stored Procedure for the table prameter.

Getting Started

The below steps are helps to create storeprocedure in sql that accepts table as parameter.

  1. Create your own data type (User Defined data type) as table

    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, alphanumeric employee IDs, 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:

     CREATE TYPE datatypename  
 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 data type should be the same as your table that you want to insert data into. If your table contains an identity column, then you no need to create a column of your user-defined data type. The following structure defines the structure of the table where I want to insert the data.

     CREATE TABLE [Students](   
   [StudentID] [int] IDENTITY(1,1) NOT NULL,   
   [StudentName] [nvarchar](30) NOT NULL,   
   [StudentNumber] [varchar](15) NOT NULL,   
   [StudentClass] [varchar](50) NOT NULL)   
 )

    Create Stored Procedure with Table Parameter

    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.

     CREATE TYPE [dbo].[Student] AS TABLE(   
   [StudentName] [nvarchar](30) NULL,   
   [StudentNumber] [varchar](100) NULL,   
   [StudentClass] [varchar](50) NULL   
 )

    Create Stored Procedure with Table Parameter

    In the preceding as I said above, I have not declared the column [StudentID] because it is an identity column.

  2. Create Stored Procedure and User your DataType

    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.

     CREATE PROCEDURE ManageStudent   
 (@StudentDetails dbo.Student READONLY)   
 AS   
 BEGIN   
   INSERT INTO dbo.Students(StudentName, StudentNumber, StudentClass) SELECT StudentName,StudentNumber,StudentClass FROM @StudentDetails  
 END

    Create Stored Procedure with Table Parameter

    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.

     DECLARE @StudentDetails Student   
  INSERT INTO @StudentDetails(StudentName, StudentNumber, StudentClass)   
  VALUES ('Kailash','123','FIFT'),('Chandra','234','SIXTH'),('Behera','345','SEVENTH')   
  EXEC ManageStudent @StudentDetails

    Create Stored Procedure with Table Parameter

    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 is declaring and executing a Stored Procedure using SQL.

Summary

In this article we demonstrates how to create Stored Procedure with table parameter in SQL that accept table as parameter. Hope this article may helpful to you.

Thanks

By at