Pass a table as a parameter to a stored procedure

1. Create a new user defined table type (UDTT). A table type is conceptually like a temporary table, but you can pass this dude into a stored procedure as a parameter. This is handy if you need to move data between different processes in bulk.

CREATE TYPE UsersType AS TABLE
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	UserName nvarchar(100),
	SystemId nvarchar(100),
	[Admin] bit
);

2. Next, you’ll declare a table variable of type UsersType, the same way you would any other data type. Likewise, insert data into it as you would with any table.

DECLARE @UsersSubset UsersType;

INSERT INTO @UsersSubset (UserName, SystemId, [Admin]) VALUES ('jlee', 'MTA', 1);
INSERT INTO @UsersSubset (UserName, SystemId, [Admin]) VALUES ('smara', 'CommSat', 0);

SELECT * FROM @UsersSubset;

Running the SELECT results in…

| Id | UserName | SystemId | Admin |
|----+----------+----------+-------|
|  1 | jlee     | MTA      |     1 |
|  2 | smara    | CommSat  |     0 |

3. You can read those values in your stored procedure, and execute any valid queries against it as needed. Note that the data inside the table type cannot be changed, so you must declare it with the READONLY option.

CREATE PROCEDURE [dbo].[proc_Sysadmins] 

@Users UsersType READONLY -- The table type itself is immutable

AS
BEGIN
  SELECT DISTINCT UserName, SystemId FROM @Users WHERE Admin = 1;
END
GO

4. Finally you can pass @UsersSubset table type into the stored proc as a parameter to be processed however you like.

EXEC [dbo].[proc_Sysadmins] @UsersSubset;

| UserName | SystemId |
|----------+----------|
| jlee     | MTA      |