none
SSRS. Not passing fields/parameters to report when stored procedure keeps parameters inside IF statement RRS feed

  • Question

  • So what I mean:

    I have stored procedure like this:

    CREATE PROCEDURE sp_Example
    @param1 NVARCHAR(20) = '',
    @param2 DATE = ''
    AS
    BEGIN
       IF (SELECT COUNT(*) FROM Table1 WHERE (Name = @param1 OR @param1 = '') AND (StartDate = @param2 OR @param2 = '')) > 2
       BEGIN
          SELECT Name, Date, Price, Etc
          FROM Table2
          WHERE (Name = @param1 OR @param1 = '') AND (StartDate = @param2 OR @param2 = '')
       END
    
       IF (SELECT COUNT(*) FROM Table1 WHERE (Name = @param1 OR @param1 = '') AND (StartDate = @param2 OR @param2 = '')) < 2
       BEGIN
          SELECT Name, Date, Price, Etc
          FROM Table3
          WHERE (Name = @param1 OR @param1 = '') AND (StartDate = @param2 OR @param2 = '')
       END
    END

    So in stored procedure are some input parameters and they are passed into IF statement.


    If I use this stored procedure as report's dataset (Microsoft Visual Studio 2013) in following:

    Add Dataset > Query Type: Stored Procedure > sp_Example It do NOT get any fields, but get parameters


    If I use this stored procedure in following:

    Add Dataset > Query Type: Text > EXECUTE sp_Example It get all required fields, but do NOT get parameters

    Of course If I add manually parameters or fields It not working.


    If I change IF statement in stored procedure something like:

    IF (1 < 3)
    BEGIN
          SELECT Name, Date, Price, Etc
          FROM Table3
          WHERE (Name = @param1 OR @param1 = '') AND (StartDate = @param2 OR @param2 = '')
    END

    It normally working in report (getting all fields and parameters). So problem is that I pass parameters to IF statement. Have you any ideas how to pass parameters to IF statement and get It correctly working on report?

    Friday, March 20, 2015 7:07 AM

Answers

  • Hi Stanisolvas,

    Per my understanding that you are experending some issue when using the stored procedure to create the dataset, you can't get the fields to display under the dataset, right?

    I have tested on my local environment with the same script as your and can't reproduce the issue, Please try to check and provide more details information according to below points:

    1. What do you mean of not get any fields, Is that mean no table header along with the value will display? If you got any error message, please provide it.
    2. Did you use any temp table in the stored procedure, if so, the issue can be caused by the temp table you are using, please don't use temp table in it which will cause no fields display.
    3. If you use the second method to execute the procedure, please check to make sure you have added the script like below to make the parameters display in the report:
      exec sp_Example @Param1=@Param1,@Param2=@Param2
    4. Please execute the stored procedure in the SQL Server Management Studio like below to see if the stored procedure will works fine:
      exec sp_Example @Param1='Test1',@Param2='2014-10-15'
      If this work fine in the management studio, please try to create an new report to re-add this procedure.

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    Tuesday, March 24, 2015 8:23 AM
    Moderator

All replies


  • Hi,

    You can write your query in expression as below;



    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    Friday, March 20, 2015 9:30 AM
  • Hi Stanisolvas,

    Per my understanding that you are experending some issue when using the stored procedure to create the dataset, you can't get the fields to display under the dataset, right?

    I have tested on my local environment with the same script as your and can't reproduce the issue, Please try to check and provide more details information according to below points:

    1. What do you mean of not get any fields, Is that mean no table header along with the value will display? If you got any error message, please provide it.
    2. Did you use any temp table in the stored procedure, if so, the issue can be caused by the temp table you are using, please don't use temp table in it which will cause no fields display.
    3. If you use the second method to execute the procedure, please check to make sure you have added the script like below to make the parameters display in the report:
      exec sp_Example @Param1=@Param1,@Param2=@Param2
    4. Please execute the stored procedure in the SQL Server Management Studio like below to see if the stored procedure will works fine:
      exec sp_Example @Param1='Test1',@Param2='2014-10-15'
      If this work fine in the management studio, please try to create an new report to re-add this procedure.

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    Tuesday, March 24, 2015 8:23 AM
    Moderator
  • Hi Vicky,

    Good idea!

    But the problem is that using this exec sp_Example @Param1=@Param1 method will not work for me since I have multiple values, I ended up getting this error:

        Must pass parameter number 2 and subsequent parameters as '@name = value'

    Anyway to get around this error?

    Thanks.

    Tuesday, July 5, 2016 9:17 PM