none
How to write multiple select statements in single stored procedure

    Question

  • I am having a report with 8 datasets.I am directly writting queries in the datasets and executing the report.Now I am thinking to put all 8 datasets in a single stored procedure in the database in  following way

    create procedure <Report1>

               

     

    @pQueryName varchar(50)

                 @parameter1 varchar(20)

                 @parameter2 varchar(20)

                  @parameter3 varchar(20)

     

    Begin

           IF @pQueryName ='1'

            SELECT  column1,column2 from table1 where some condition

          IF @pQueryName='2'

            SELECT calculated(Column3),column4 from table2 where  @parmeter1 and @parameter2

         IF @pQueryName='3'

           SELECT calculated(column4) from table3 where @parameter1 and @parameter3 

     

    Then In the report in each dataset i  was calling  the following way

    Command type ='Text'

    Exec Report1  '1', @parameter1, @parameter2, @parameter3

    Exec Report1  '2', @parameter1, @parameter2, @parameter3

    Exec Report1  '3', @parameter1, @parameter2, @parameter3

    When I run in dataset it was fi9. When I run the report In all the dataset i am seeing the same column names i.e column names of the

    @pQueryName ='1'

            SELECT  column1,column2 from table1 where some condition

    end

     and report is not running.

    So can any one say where I am doing wrong.

    Wednesday, August 11, 2010 4:36 AM

Answers

  • Hi,

    Currently, you have to sperate it into multilple selects in each datasets in reporting services.

    thanks,
    Jerry

    Thursday, August 12, 2010 10:27 AM
    Moderator

All replies

  • I am having a report with 8 datasets.I am directly writting queries in the datasets and executing the report.Now I am thinking to put all 8 datasets in a single stored procedure in the database in  following way

    create procedure <Report1>

               

     

    @pQueryName varchar(50)

                 @parameter1 varchar(20)

                 @parameter2 varchar(20)

                  @parameter3 varchar(20)

     

    Begin

           IF @pQueryName ='1'

            SELECT  column1,column2 from table1 where some condition

          IF @pQueryName='2'

            SELECT calculated(Column3),column4 from table2 where  @parmeter1 and @parameter2

         IF @pQueryName='3'

           SELECT calculated(column4) from table3 where @parameter1 and @parameter3 

     

    Then In the report in each dataset i  was calling  the following way

    Command type ='Text'

    Exec Report1  '1', @parameter1, @parameter2, @parameter3

    Exec Report1  '2', @parameter1, @parameter2, @parameter3

    Exec Report1  '3', @parameter1, @parameter2, @parameter3

    When I run in dataset it was fi9. When I run the report In all the dataset i am seeing the same column names i.e column names of the

    @pQueryName ='1'

            SELECT  column1,column2 from table1 where some condition

     and report is not running.

    So can any one say where I am doing wrong.

         

    Monday, August 09, 2010 6:13 PM
  • Hi,

    Go to the Parameters tab in the dataset and map the stored procedures parameters with the report parameters. Hopefully that could help.

    Regards

     


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH
    Wednesday, August 11, 2010 4:40 AM
  • Right click on dataset and select stored procedure and select from dropdownlist, click on refresh fields it will display all fields and check whether you have got expected fields which you want to display in the report.
    Nanda - Misys Software Solutions,Bangalore
    Wednesday, August 11, 2010 5:21 AM
  • Hi,

    Currently, you have to sperate it into multilple selects in each datasets in reporting services.

    thanks,
    Jerry

    Thursday, August 12, 2010 10:27 AM
    Moderator
  • Hi Jerry,

     

    Now I have written separate datasets.But I want to have a single stored procedure is it possible to have.

    Thursday, August 12, 2010 2:11 PM
  • Hi Krishna143

    What I understand is that your requirement is to fetch the data from different table on some conditions. So for this follow these steps and you can solve this in the stored procedure.

    1. Pass one more parameter to the SP, and on the basis of this parameter you can determine that which select statement will run.

    2. Make sure that each of your select statement will return the same column name.

    I hope all your select statement is returning the smae number of columns.

    Thanks

    Nishant

    Friday, August 13, 2010 8:59 AM
  • Hi Krishna,

    Any stored procedure will be a logical group of statements. In your case, when you group all 8 SELECT statements related to data sets, firstly, you need to check if they are related and whether you can group them. If they are not logically related, it doesnt make sense to create a single consolidated stored procedure.

    Please check the applicability and relevance and implement accordingly.

    Regards,

     


    Phani Note: Please mark the post as answered if it answers your question.
    Friday, August 13, 2010 9:35 AM