How to call a SP from RS

Question

Post reply

How to call a SP from RS

Benoit Merlin

Grasshopper

Points: 13
More actions
September 1, 2004 at 9:03 am

#108981

I am fairly new to SQL and reporting services so you will have to excuse me.
Created a table with 3 groups which work just fine.
Now I want to call a Stored Procedure which takes 2 parameters and returns a single value.
I have been searching everything and do not know where I put the code and of course what is the syntax.
Thanks in advance for the help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jarretg SSCommitted Points: 1706 More actions · Answer 1

Here are a couple of ways you can do this:

1. In the data tab, use the following type of syntax as your command.

Example: EXEC stored_proc_name @param1, @param2

Click the 'Generic Query Designer' button (for me it is 4 over from the dataset drop down box) and make sure it has Text selected as the Command type. Hit the refresh button to show your returned fields from the stored procedure. When you go to preview the report, there should be input boxes at the top for each of your input parameters.

***If you know that the parameters will not vary from run to run, go ahead and type them as part of the command. In this case, the input boxes will not show up as they are not needed.

Example: EXEC stored_proc_name 'value1', 'value2'

2. In the data tab, just type the name of the stored procedure as your command. Click the 'Generic Query Designer' button and make sure you have StoredProcedure selected as the Command type. Hit the refresh button to show your returned fields from the stored procedure. When you go to preview the report, there should be input boxes at the top for each of your input parameters to the stored procedure.

Hope this helps.

Jarret

Benoit Merlin Grasshopper Points: 13 More actions · Answer 2

Thanks a lot Jarret

I was in a bit of hurry so I used a MS incident call. The tech gave me the same information which is great for other things that I want to do.

By the way, well explained answer

What I want to do is the following:

I have 3 groups in a table, I want to take the value in the header of each group and pass it to a SP as parameters so that I can return a value to a cell within that table.

The value would be displayed on the footer of the first group.

Hope this makes sense, once again thanks for the help.

Benoit Merlin Grasshopper Points: 13 More actions · Answer 3