Passing Entire table as Parameters of Stored Procedure
Introduction
In this article I am trying to demonstrate, how to pass a entire table as a parameters of stored procedure. But this facility is from MS SQL Server 2008 only. SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions.
Let's start with an example
Here I am taking a simple example to understand the process only. I have two table object and I want to copy all the data from first table to second one by using a stored procedure
Step-1 [ Create the Base Table Objects ]
-- Source Table Objects
IF OBJECT_ID(N'dbo.Tbl_EMPLOYEE', N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[Tbl_EMPLOYEE];
END
GO
CREATE TABLE [dbo].[Tbl_EMPLOYEE]
(EMPID INT NOT NULL IDENTITY PRIMARYKEY,
EMPNAME VARCHAR(50) NOT NULL);
GO
-- Inserting Records in my Source Table
INSERT INTO [dbo].[Tbl_EMPLOYEE]
(EMPNAME)
VALUES ('Joydeep Das'),
('Chandan Bannerjee');
-- Now the Destination Table Object
IF OBJECT_ID(N'dbo.Tbl_EMPLOYEE_BACKUP', N'U') IS NOTNULL
BEGIN
DROP TABLE [dbo].[Tbl_EMPLOYEE_BACKUP];
END
GO
CREATE TABLE [dbo].[Tbl_EMPLOYEE_BACKUP]
(EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL);
GO
My main target is to copy data from Tbl_EMPLOYEE to Tbl_EMPLOYEE_BACKUP
Step-2 [ Create Table Type ]
Now we are setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine.
This allows us to define the type of table and reuse it as needed in our stored procedure code.
IF EXISTS(SELECT * FROM sys.table_types WHERE namelike 'type_Employee')
BEGIN
DROP TYPE type_Employee;
END
GO
CREATE TYPE type_Employee AS TABLE
(
EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL
);
Step-3 [ Create Stored Procedure ]
IF OBJECT_ID(N'dbo.sproc_EMPDTLS', N'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[sproc_EMPDTLS];
END
GO
CREATE PROCEDURE [dbo].[sproc_EMPDTLS]
(
@p_TableVariable type_employee READONLY
)
AS
BEGIN
INSERT INTO [dbo].[Tbl_EMPLOYEE_BACKUP]
(EMPID, EMPNAME)
SELECT EMPID, EMPNAME
FROM @p_TableVariable;
END
Step-4 [ Execute Stored Procedure ]
First, we must declare a variable of type type_Employee. We don't need to define the structure of the table again because it was defined when we created the type.
BEGIN
DECLARE @DataTable AS type_employee;
INSERT INTO @DataTable
SELECT * FROM [dbo].[Tbl_EMPLOYEE];
EXEC [dbo].[sproc_EMPDTLS]
@p_TableVariable = @DataTable;
END
Step-5 [ Observe the Output ]
SELECT * FROM [dbo].[Tbl_EMPLOYEE];
GO
SELECT * FROM [dbo].[Tbl_EMPLOYEE_BACKUP];
GO
