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

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

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

Answers

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).

    George
    blog | twitter

    Friday, April 05, 2013 11:42 AM
    Answerer
  • 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