sql - Pass multiple values to get CSV output -

create procedure temp ( @mid smallint ) begin  select tranid, [monthvalue]=(case when @mid=1 jan     when @mid=2 feb     when @mid=3 mar     when @mid=4 apr     when @mid=5 may     when @mid=6 jun     when @mid=7 jul     end)          tbltran m  tranid=1 ,        m.month = @mid end 

this stored procedure parameter @mid i'm using generate report using ssrs.
if single value passed parameter works fine.

for example-

transaction table

tranid | apr |  may  | jun   | jul   

---

1     |  50  |   30  |  11   |   30    2     |  51  |   39  |  100  |   30 

if execute with
exec 4
result expect

tranid  |  monthvalue      1       |   50   **-- ie aprils value** 

but need pass multiple values parameter

exec 4,5,6

and desired result should be

tranid  |  monthvalue          1       |   50,30,11     ***-->comma separated values of columns   

how can acheive result this??

you can use split function, e.g.

create function dbo.splitints (     @list       varchar(max),     @delimiter  varchar(32) ) returns table     return      (       select item = convert(int, item)          (select number, item = ltrim(rtrim(substring(@list, number,            charindex(@delimiter, @list + @delimiter, number) - number)))         (select row_number() on (order [object_id])           sys.all_objects) n(number)         number <= convert(int, len(@list))         , substring(@delimiter + @list, number, len(@delimiter)) = @delimiter       ) y     ); 

then pass parameters in single string rather separate parameters (you can ssrs according post):

exec dbo.procedurename @param = '4,5,6'; 

here quick example using declared variable , dynamic sql output want:

create table dbo.transactionstest (tranid int primary key, apr int, may int, jun int, jul int);  insert dbo.transactionstest values (1,50,30,11 ,30),    (2,51,39,100,30);   declare @months varchar(32) = '4,5,6';    declare @sql nvarchar(max) = n'';  select @sql = 'select tranid,monthvalue=' + stuff( (select '+'',''+rtrim('    + convert(char(3),datename(month, dateadd(month, item-1, 0))) + ')'    dbo.splitints(@months,',')    order item   xml path, type).value('.[1]', 'nvarchar(max)'),1,5,'') + '    dbo.transactionstest order tranid;';  print @sql;  exec sp_executesql @sql; 

results:

-- printed in messages pane:  select tranid,monthvalue=rtrim(apr)+','+rtrim(may)+','+rtrim(jun)      dbo.transactionstest order tranid;  -- grid/text results:  tranid  monthvalue ------  ---------- 1       50,30,11 2       51,39,100 

sqlfiddle demo