Adding onto @Matthew Sontum's answer using Table Valued Parameters:
You can INSERT into the user defined type like any other table, and this syntax may be more intuitive for some people.
Type definition:
CREATE TYPE UserList AS TABLE ( UserID INT );
Alternate Method to populate UserList
Example: Populate the UserList Type with all the UserIds of Users with names starting with 'J'
DECLARE @UL UserList;
INSERT INTO @UL
(
UserID
)
SELECT UserId FROM dbo.UserTable userTable
WHERE userTable.Username LIKE 'J%'
As per Matthew's answer, you can then call the stored procedure like so:
EXEC dbo.get_user_names @UL, @username OUTPUT;
Check Table Parameter before executing Stored Procedure logic
This was not a requirement for the OP's original question, but we often want to check if the incoming table parameter has values before proceeding in order to avoid any potentially expensive db operations.
Since it is a table, and not a standard variable, it is not feasible to do a NULL check. Instead:
CREATE PROCEDURE [dbo].[get_user_names]
@user_id_list,
@username varchar (30) output
as
BEGIN
IF EXISTS (SELECT TOP 1 1 FROM @user_id_list)
BEGIN
select last_name+', '+first_name
from user_mstr
where user_id in @user_id_list
END
--ELSE avoid the expensive operation
END