locked
Using Fields as in parameters in stored procedures RRS feed

  • Question

  • Hi...

    I have created  a stored procedure with 2 input parameters, 1 comes from a parameter and the other from a dataset.

    When i set these as the parameters for my stored procedure i get this error :

    Fields cannot be used in query paramater expressions.

    Please Help

    Rebekah

    Wednesday, August 17, 2011 6:07 AM

Answers

  • Hi Rebekah,

    Thanks for your post.


    According to your description, you would like to pass a parameter and a dataset total field values to your stored procedure, due to your parameter is come from a dynamic calculated field which is based on report execute process not a database T-Sql process, so I would suggest you to build a drill through report, Pass the two parameters from your main report to the sub report which your stored procedure referred in. please refers to the steps below:

    1.       Create an individual report for the stored procedure which you care about with two report-level parameters, name the report as subreport.

    2.       In your main report (you had created before, right-click the total value textbox (the total value which you want to pass to stored procedure), select Text Box Properties.

    3.       Switch to the Action tab, check the Go to report item.

    4.       Select the subreport in the Specify a report drop down list.

    5.       Click Add to specify the parameter and total value (which you would pass them to your stored procedure) to the relevant parameters of the subreport.

    6.   Click ok.

    After you complete the steps above, click preview to run your report, and then click the total value filed to showing the subreport which will show the data your stored procedure retrieved from database.


    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Zilong Lu Saturday, August 27, 2011 9:41 AM
    Saturday, August 20, 2011 9:31 AM

All replies

  • Rebakah,

     

    The reason for this is you cannot directly use any report dataset field acting as a parameter to another dataset/stored procedure.

     

    • You can create another Parameter (Param2) and refer it the value from, said dataset.
    • If you don't want user to see that parameter, just hide this.
    • Then, pass the 2 parameter to your stored procedure.

     

    Please, let me know if you require further details!


    Regards,
    Manoj
    *Happy to help
    Wednesday, August 17, 2011 6:13 AM
  • Hi Manoj,

    I tried this, hit another problem.

    I need my stored procedure to be called after that dataset value is computed, is there a way for me to set this order.

    At the moment the stored procedure is being called first, and the parameter its expecting from the dataset is not computed yet.

    Thanks

    Rebekah

    Wednesday, August 17, 2011 6:19 AM
  • Rebekah,

     

    The stored procedure you want to call, is it inside your report or in your dataset?

     

    Can you please give some details on how you are calling the stored procedure.


    Regards,
    Manoj
    *Happy to help
    Wednesday, August 17, 2011 6:57 AM
  • Hi,

    Its through dataset, where im setting the parameters for the stored procedure, on if the parameters is a total value computed when the report  is generated.

    So i need this dataset to be called first, to generate this total value.

    Thanks

    Rebekah

    Wednesday, August 17, 2011 7:23 AM
  • You can achieve this using subreport. Except for the parameter and the dataset where you are doing the calculation, move the other display element and the other dataset to the subreport. You can set the parameter for the subreport based on the dataset's field.
    Wednesday, August 17, 2011 9:14 AM
  • Rebekah,

     

    Ok, so this stored procedure is fetching data  for Total value.

     

    Now, in your scenario the Stored procedure require 2 parameters - that are created inside your report.

    One of those parameter fill its value from another dataset.

     

    So, I would suggest you to order your parameters properly.

     

    Dataset dependant on parameters of your report will be executed based on the parameter order in your report.

     

    So, those 2 parameters that you require to pass to stored procedures should be appearing first.

     


    Regards,
    Manoj
    *Happy to help
    Wednesday, August 17, 2011 9:51 AM
  • Hi Rebekah,

    Thanks for your post.


    According to your description, you would like to pass a parameter and a dataset total field values to your stored procedure, due to your parameter is come from a dynamic calculated field which is based on report execute process not a database T-Sql process, so I would suggest you to build a drill through report, Pass the two parameters from your main report to the sub report which your stored procedure referred in. please refers to the steps below:

    1.       Create an individual report for the stored procedure which you care about with two report-level parameters, name the report as subreport.

    2.       In your main report (you had created before, right-click the total value textbox (the total value which you want to pass to stored procedure), select Text Box Properties.

    3.       Switch to the Action tab, check the Go to report item.

    4.       Select the subreport in the Specify a report drop down list.

    5.       Click Add to specify the parameter and total value (which you would pass them to your stored procedure) to the relevant parameters of the subreport.

    6.   Click ok.

    After you complete the steps above, click preview to run your report, and then click the total value filed to showing the subreport which will show the data your stored procedure retrieved from database.


    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Zilong Lu Saturday, August 27, 2011 9:41 AM
    Saturday, August 20, 2011 9:31 AM