How do I pass a list as a parameter in a stored procedure?

by

The preferred method for passing an array of values to a stored procedure in SQL server is to use table valued parameters.

First you define the type like this:

CREATE TYPE UserList AS TABLE ( UserID INT );

Then you use that type in the stored procedure:

create procedure [dbo].[get_user_names]
@user_id_list UserList READONLY,
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in (SELECT UserID FROM @user_id_list)

So before you call that stored procedure, you fill a table variable:

DECLARE @UL UserList;
INSERT @UL VALUES (5),(44),(72),(81),(126)

And finally call the SP:

EXEC dbo.get_user_names @UL, @username OUTPUT;

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How to SELECT FROM stored procedure
  3. Function vs. Stored Procedure in SQL Server
  4. SQL Server Insert if not exists
  5. Stored procedure or function expects parameter which is not supplied
  6. What is a stored procedure?
  7. How to assign an exec result to a sql variable?
  8. SQL Call Stored Procedure for each Row without using a cursor
  9. SQL Server SELECT into existing table
  10. How do I execute a stored procedure once for each row returned by query?
  11. How to check if a stored procedure exists before creating it
  12. Execute Stored Procedure from a Function
  13. SQL: Select dynamic column name based on variable
  14. SQL : in clause in stored procedure:how to pass values
  15. SQL Server silently truncates varchar’s in stored procedures
  16. Split values over multiple rows [duplicate]
  17. Must declare the scalar variable
  18. Why execute stored procedures is faster than SQL query from a script?
  19. Stored procedure that exports data into csv files only exports to one file
  20. Custom Date/Time formatting in SQL Server
  21. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  22. Insert Update stored proc on SQL Server
  23. SQL Server: should I use information_schema tables over sys tables?
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. How to pass a temp table as a parameter into a separate stored procedure
  26. Eliminate and reduce overlapping date ranges
  27. How to execute a stored procedure inside a select query
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment