A. Basically you can't - SQL Server has no array type - ANSI SQL 92 does not specify array support.
You could simulate an array by passing one or more varchar(255) fields with comma-separated values and then use a WHILE loop with PATINDEX and SUBSTR to extract the values.
The more usual way to do this would be to populate a temporary table with the values you need and then use the contents of that table from within the stored-procedure. Example of this below
create procedure mytest @MyParmTempTable varchar(30)
as
begin
-- @MyParmTempTable contains my parameter list...
-- For simplicity use dynamic sql to copy into a normal temp table...
create table #MyInternalList (
list_item varchar( 2 ) not null
)
set nocount on
insert #MyInternalList
exec ( "select * from " + @MyParmTempTable )
set nocount off
-- It is now easier to join..
select *
from sysobjects
where type in ( select list_item from #MyInternalList )
end
go
To call..
create table #MyList (
list_item varchar( 2 ) not null
)
insert #MyList values ( 'S' )
insert #MyList values ( 'U' )
insert #MyList values ( 'P' )
exec mytest "#MyList"
