sql server - SQL - parametrized procedure with multiple parameters as array -

i have simple procedure:

create procedure [report] @statusvalue varchar(200) = '%' begin select * sometable upper(@statusvalue) end 

i'd provide user set multiple statusvalue. because there 6 levels of statusvalue in table, i'd provide user define required statusvalue procedure parameters - array.

i don't know, how works - i'm new in area - i'm supposing have procedure one:

exec report @statusvalue = 'statusvalue1|statusvalue2|statusvalue3' 

do happen know, how can adjust procedure have required output. many in advance.

use following user defined function return values delimited string (say pipe):

create function [dbo].[stringlist_to_table]     (@list      varchar(8000),     @delimiter nchar(1) = n',')  returns @tbl table (value varchar(8000)) begin     declare @pos      int,         @tmpstr   varchar(8000),         @tmpval   varchar(8000);      set @tmpstr = @list;     set @pos = charindex(@delimiter , @tmpstr);      while @pos > 0     begin         set @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))         insert @tbl (value) values(@tmpval)         set @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))         set @pos = charindex(@delimiter, @tmpstr)     end      insert @tbl(value) values (ltrim(rtrim(@tmpstr)));     return end 

now use following procedure required output:

create procedure [report]     @statusvalue varchar(200) = '%' begin      declare @iterator int = 1;     declare @total int = 1;     declare @keyword varchar(100) = '';      select row_number() on (order value) sno, value keyword     #temp     dbo.stringlist_to_table(@statusvalue, '|')      select *      #output     sometable     1 = 0;      select @total = max(sno), @iterator = min(sno)     #temp      while (@iterator <= @total)     begin         select @keyword = '%' + keyword + '%'         #temp         sno = @iterator;          insert #output         select *          sometable         @keyword          set @iterator = @iterator + 1;     end      select *     #output;      drop table #output, #temp; end