How to pass a table variable to a stored procedure

Problem: sometimes we like to pass a table values to a stored procedure to send multiple values.

Solution:

CREATE TYPE Starting SQL server 2008 or higher.

Using this above DDL statements we can create a user defined table type to hold multiple columns.

You can also create an index on the columns you want to by specifying it as primary key, for performance reasons or to give a better join access this will be helpful.

Example:

Let’s create a table type called EmployeeType which has two columns employeeid and employeetype

and access this table to use a parameter inside a stored procedure.  

1.       Create a table type.

2.       Create a procedure with table type variable as a parameter.

3.       Declare a table type variable and call the procedure with the table type variable as parameter

Sample script below:

 -- Create a table type to hold multiple values.

  CREATE TYPE [dbo].[EmployeeType] AS TABLE

(

                [EmployeeID] [int] NOT NULL Primary key ,

                [employeetype] Varchar(32)  Null            

)

GO

-- Create a procedure to use the table type to pass as parameter.

Create  Procedure dbo.getallemployeesbyempID

@employeetable [EmployeeType] readonly

AS

BEGIN

SELECT

                E.[EmployeeID]

      ,E.[Name]

      ,E.[PhoneNumber]

                  ,E.[employeetype]

  FROM [dbo].[Employees] E

  INNER JOIN (Select* from @employeetable) ET ON

  ET.[EmployeeID]=E.[EmployeeID]

  AND ET.[employeetype]=E.[employeetype]

 

END

GO

Employee table has the following rows:

/**********============= Execution ===============*****************/

--declare the variable as table type

Declare @EmployeeID as [EmployeeType]

-- insert into the table type variable to load the parameter with multiple employee id's

Insert Into @EmployeeID (employeeid,employeetype)

VALUES

(5,'Developer'),

(11,'Tester'),

(21,'Developer')

--execute the procedure to pass the table type variable which is loaded.

EXEC dbo.getallemployeesbyempID @EmployeeID;