Pass Table as Parameter to Stored Procedure

In this article let us see how to pass table as parameter to stored procedure. Create a User Defined Table with same schema as that of the Table Valued parameter that we need to pass to the Stored Procedure. I have created a new table named Customer in Sql.

Customer Table to Pass Table as Parameter to Stored Procedure

CREATE TABLE [dbo].[Customer](
  [Cust_ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NULL,
  [Age] [int] NULL,
  [Gender] [nvarchar](50) NULL,
  [Address1] [nvarchar](50) NULL,
  [Phone_number] [decimal](18, 0) NULL,
  [Email_ID] [nvarchar](50) NULL,
  [Salary] [decimal](18, 0) NULL,
  [DummyAge] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
  [Cust_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

Create a user defined table type with same schema as like customer table.

 

CREATE TYPE [dbo].[CustomerType] AS TABLE(
Cust_ID int NULL,
Name nvarchar(50),
Age int,
Gender nvarchar(50),
Address1 nvarchar(50),
Phone_number decimal(18, 0),
Email_ID nvarchar(50),
Salary decimal(18, 0),
DummyAge int
)
GO

At last create a stored procedure which accepts user defined table type as input and insert records into the table.

 

CREATE PROCEDURE [dbo].[Insert_Customers]
@tblCustomers CustomerType READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Customers(Cust_ID, Name, Age,Gender,Address1,Phone_number,Email_ID,Salary,DummyAge)
SELECT Cust_ID, Name, Age,Gender,Address1,Phone_number,Email_ID,Salary,DummyAge FROM @tblCustomers
END

 

Now a table variable of type CustomerType is created and then some records are inserted it. Then the table variable is passed as parameter to the Stored Procedure and the Stored Procedure is executed which finally inserts the records into the Customers Table.

DECLARE @tblCustomers CustomerType

INSERT INTO @tblCustomers
SELECT 1, 'Jimmy', 21,'Male','United States','7894561230','jimmy@gmail.com',25000,10
UNION ALL
SELECT 2, 'dilak',24,'Female', 'India','7501288808','kiruba042@gmail.com',20000,24


EXEC Insert_Customers @tblCustomers