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','firstname.lastname@example.org',25000,10 UNION ALL SELECT 2, 'dilak',24,'Female', 'India','7501288808','email@example.com',20000,24 EXEC Insert_Customers @tblCustomers