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.
  • 3445

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:

empTable.jpg

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:

empTableUserDefined.jpg

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:

CreateProcedure.jpg

Now execute the Stored Procedure

declare @empVariable as empType
insert into @empVariable([emp_id], [em_name]) values(1,'abc')
exec dbo.empDetailsProcedure @empVariable

Output:

ExecuteProcedure.jpg

Display The Emp Table

Now select the above inserted data.

 select * from emp

Output:

select.jpg

© 2013 dotNetheaven. All rights reserved.