Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation


  •     Create procedure temp
        @MID smallint

        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
    TblTran as M
         where TranID=1 and
               M.Month = @MID

    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   

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

    • Edited by Friday, April 05, 2013 11:32 AM
    Friday, April 05, 2013 11:26 AM


All replies

  • You can try Table valued parameters...

    Use Table-Valued Parameters

    SQL Server 2008: Table Valued Parameters

    A series of article from Erland Sommarskog on passing arrays to stored procedure is below:

    Arrays and Lists in SQL Server

    Krishnakumar S

    Friday, April 05, 2013 11:40 AM
  • And the comma separation is a display issue that should be handled by your presentation layer (e.g. front-end application or report).

    blog | twitter

    Friday, April 05, 2013 11:42 AM
  • Other option can be to use a user define split function and do the necessary changes in your select statement.

    Here is the link. 

    Regards, RSingh

    Friday, April 05, 2013 2:16 PM
  • Why do you think that First Normal Form (1NF) does not matter? What did Dr. Codd miss that you see? 

    Why are you using a procedure when all you need is a CASE expression? The answer is that you are still thinking in  procedural code and do not know how a declarative set-oriented language like SQL works. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, April 05, 2013 5:49 PM