User Defined Table Types And Table Valued Parameters In MSSQL

In this article, we are going to create a User Defined Table Types and will use it as a parameter in Stored Procedure which is called Table-Valued Parameters.

User-Defined Table Types is been used when we want to pass a list of parameters to a stored procedure or a function.

suppose we want to insert multiple data into one table using a stored procedure or a function at that time we are either using multiple parameters or called it multiple times for inserting data. but using User Defined Table Types we are able to pass list or predefine table set/data set to the stored procedure and it will insert data set into the target table.

User-Defined Table Types and Table-Valued Parameters concepts were introduced in SQL Server 2008.

so basically we can pass multiple rows of data to a stored procedure or a function without passing multiple parameters or creating a temporary table.

I have already created one table named Customer. you can take the reference as below.

As you can see in the above images, there are also some data in the Customer table. now we are going to create User Defined Table Types and Stored Procedure with Table-Valued Parameters.

Create New User-Defined Table Type

CREATE TYPE UDT_Customer AS TABLE  
(  
    Customer_No int NOT NULL,  
    CustomerName nvarchar(50) NOT NULL
)

We can also check all User Defined Table Types under Programmability -> Types -> User-Defined Table Type.

here we can also find our created UDT_Customer User Defined Table Type.

Create Stored Procedure with Table-Valued Parameters

CREATE PROCEDURE Customer_Save
(
  @Customers [UDT_Customer] READONLY
)
AS
BEGIN  

  INSERT INTO dbo.Customer  
  (  
    Customer_No,  
    CustomerName
  )
  SELECT
    c.Customer_No,
    c.CustomerName
  FROM @Customers c

END

here we are making Table-Valued Parameter as READONLY,  which means we can’t do any DML operations like INSERT, UPDATE or DELETE on a table-valued parameter on it. even if you try to create a stored procedure without READONLY then you will get the error of the Read-Only option.

Now, after successfully creating the stored procedure we are going to execute it for inserting data.

By following the below SQL script you can execute the stored procedure by passing the Table-Valued Parameter.

DECLARE @Customers AS [UDT_Customer]
 
INSERT INTO @Customers
VALUES
(103, 'Faisal'),
(104, 'Sagar'),
(105, 'Deepak')

EXEC Customer_Save @Customers

The above SQL script will insert data into User-Defined Table Types variable which we have created. and then we have passed it into the stored procedure as a parameter.

It will insert multiple records in the customer table after the successful execution of our stored procedure.

Let’s check the data of the Customer table.

So it’s a basic way to create User-Defined Table Types and use them as Table-Valued Parameters.

Hope it will be useful for you. Thank You 🙂

Also check, Generate C# Model Class From Table In MSSQL