Creating Stored Procedure with Table as Parameter in C#


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

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, 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:

     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 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.

     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)   

    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   

    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)   
       INSERT INTO dbo.Students(StudentName, StudentNumber, StudentClass) SELECT StudentName,StudentNumber,StudentClass FROM @StudentDetails  

    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  

    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.

Kailash Chandra Behera

No comments:

Post a Comment