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.
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.
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.
4. Finally you can pass @UsersSubset
table type into the stored proc as a parameter to be processed however you like.
| UserName | SystemId |
|----------+----------|
| jlee | MTA |