Pass multiple values to get CSV output

Question

Create procedure temp
(
@MID smallint
)
as
Begin

select TranID,
[MonthValue]=(CASE WHEN @MID=1 THEN Jan
    WHEN @MID=2 THEN Feb
    WHEN @MID=3 THEN Mar
    WHEN @MID=4 THEN Apr
    WHEN @MID=5 THEN May
    WHEN @MID=6 THEN Jun
    WHEN @MID=7 THEN Jul
    END)
    FROM 
    TblTran as M
 where TranID=1 and
       M.Month = @MID
end

This is a stored procedure with a parameter @MID that i'm using to generate a report using SSRS.
If a single value is passed to the parameter it works fine.

For example-

Transaction Table

TranID | Apr |  May  | Jun   | Jul  

---

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

if i execute with
Exec 4
the result is what i expect

TranID  |  MonthValue    

1       |   50   **-- ie Aprils value**

But I need to pass multiple values to the parameter
like

exec 4,5,6

and desired result should be

TranID  |  MonthValue        

1       |   50,30,11     ***-->Comma Separated values of columns  

how can i acheive result like this??

Answer 1

A stored procedure must have a finite number of predefined parameters and besides, what you are trying to achieve does not warrant a stored procedure. A better solution would be to store the values in a table create table (mid smallint, monthtext varchar(20)) and look up what you need.

Note from the looks of it you could also use datetime functionality to return the number of the month 1-12.

DECLARE @Mth smallint SET @Mth = 11 SELECT DateName(mm,DATEADD(mm,@Mth,-1)) as [MonthName]

Answer 2

You could define multiple values in the definition with defaults of null so you only pass in the number of items you want.

create procedure fred
(
  @i1 int = null,
  @i2 int = null,
  @i3 int = null,
...

Then check to see if the values are null.

Bit I agree using the system functions may be a better solution.