Pass a Table as a Parameter to a Stored Procedure in SQL Server 2008
In this article, I described how to pass a table as a parameter to a Stored Procedure in SQL Server 2008.
Introduction
In this article, I described how to pass a table as a parameter to a Stored Procedure in SQL Server 2008. To implement this we create a table named emp then create a user-defined table type and then we will pass the user defined table type as a parameter to a Stored Procedure. So let's take a example of how to pass a table as a parameter to a Stored Procedure.
Example
Creation of table
First we create a table named emp.
create table [dbo].[emp]
(
[emp_id] [int] NULL,
[em_name] [varchar](10) NULL
)
|
Output:
Creating a User-Defined Table Type
Create a user-defined table type which will be passed as a parameter in the Stored Procedure.
create type dbo.empType as table
(
[emp_id] [int] ,
[em_name] [varchar](10)
)
|
Now execute above code:
Output:
Creating a Stored Procedure
Now create the Stored Procedure named empDetailsProcedure . This Stored Procedure will accept a table valued parameter.
create procedure dbo.empDetailsProcedure
@empDetails dbo.empType readonly
as
begin
set nocount on
insert into dbo.[emp]([emp_id], [em_name])
select [emp_id], [em_name] from @empDetails
end
|
Output:
Now execute the Stored Procedure
declare @empVariable as empType
insert into @empVariable([emp_id], [em_name]) values(1,'abc')
exec dbo.empDetailsProcedure @empVariable
|
Output:
Display The Emp Table
Now select the above inserted data.
Output: