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??

So you need the output, in the comma-separated list, to be ordered in the same order as the input? e.g. if you passed in 7,5,4 would you expect output of 30,30,50 or is it ok to order by month (50,30,30)? — Aaron Bertrand, Apr 05 '13 at 12:39
Also, do you have a table for each year? If someone passed in 11,12,1 would you have to split the query across two transaction tables? — Aaron Bertrand, Apr 05 '13 at 12:44

score 0 · Answer 1 · answered Apr 05 '13 at 10:14

0

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]

answered Apr 05 '13 at 10:14

Drew R

2,988
3
19
27

From what I can tell you are storing CSV data in a text column. This is bad practice. You should store the data in proper relational form i.e a table with multiple "MonthValue[s]" referencing a transaction ID. – Drew R Apr 05 '13 at 12:41
@DrewR no, they want *output* in CSV form. The integers are stored separately in columns named `Jan`, `Feb`, etc. Questionable design at best also, but they aren't storing CSV as far as I can tell. – Aaron Bertrand Apr 05 '13 at 12:48

score 0 · Answer 2 · answered Apr 05 '13 at 12:31

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.

Aaron Bertrand · Accepted Answer · 2022-01-24T22:09:06.820

You can use a split function, e.g.

CREATE FUNCTION dbo.SplitInts
(
    @List       VARCHAR(MAX),
    @Delimiter  VARCHAR(32)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT Item = CONVERT(INT, Item) 
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y
    );

Then pass your parameters in as a single string rather than separate parameters (you can do this easily with SSRS according to this post):

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

Here is a quick example using a declared variable and dynamic SQL to get the output you 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))) + ')' 
  FROM dbo.SplitInts(@months,',') 
  ORDER BY Item
  FOR XML PATH, 
  TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,5,N'') + '
 FROM dbo.TransactionsTest ORDER BY TranID;';

PRINT @sql;

EXEC sp_executesql @sql;

Results:

-- printed in messages pane:

SELECT TranID,MonthValue=RTRIM(Apr)+','+RTRIM(May)+','+RTRIM(Jun) 
    FROM dbo.TransactionsTest ORDER BY TranID;

-- grid/text results:

TranID  MonthValue
------  ----------
1       50,30,11
2       51,39,100

SQLfiddle demo

Pass multiple values to get CSV output

3 Answers3