/************************************ Setup A table with employees and the department they work in. We want to retrieve all the employees in a list of departments. ************************************/ CREATE DATABASE TabVal GO USE TabVal GO CREATE TABLE dbo.Employees ( EmployeeID INT IDENTITY(1,1) NOT NULL CONSTRAINT EmployeeID_PK PRIMARY KEY CLUSTERED ,EmployeeName VARCHAR(100) NOT NULL ,Department VARCHAR(100) ) GO INSERT dbo.Employees (EmployeeName, Department) VALUES ('Tomas Lind', 'IT') ,('Sue', 'Accounting') ,('John', 'IT') ,('Adam', 'Accounting') ,('Hugo', 'Sales') GO /************************************ A table type must be defined ************************************/ CREATE TYPE DepartmentTableType AS TABLE ( Department VARCHAR(100) ) GO /************************************ Now create a procedure that make use of the table type ************************************/ CREATE PROCEDURE dbo.GetEmployeesInDepartments ( @tbl_Department DepartmentTableType READONLY ) AS SET NOCOUNT ON SELECT T0.EmployeeID ,T0.EmployeeName ,T0.Department FROM dbo.Employees AS T0 JOIN @tbl_Department AS T1 ON T0.Department = T1.Department GO /************************************ 1. To use the procedure, we need to create a table to pass on to the procedure. The table needs to be of the defined type. 2. Insert two departments (Accounting and Sales) 3. Excecute the procedure ************************************/ DECLARE @tbl_Demo AS DepartmentTableType INSERT @tbl_Demo VALUES ('Accounting'), ('Sales') EXEC dbo.GetEmployeesInDepartments @tbl_Demo