SSRS Report Using Stored Procedure

0
votes

I am working with an SSRS Report that uses a stored procedure.

The stored procedure [after the Use ... Set ANSI NULLS On] starts with ALTER PROCEDURE ...

While I can understand the SQL in a stored procedure, I have never used one in an SSRS Report [I only use 'straight' SQL statements].

When I use SQL as my Dataset, I can copy that SQL into SSMS and run it and see the data it returns.

With this stored procedure, how do I execute it in SSMS to see the data it returns? The stored procedure has a sample 'EXEC ...' statement with all the parameters populated ... but when I run that - no data is returned.

The SSRS report runs fine, but I want to be able to work with the stored procedure in SSMS and see the data it is returning. My goal is to be able to run the stored procedure in SSMS and then tweak it.

How do I work with this stored procedure in SSMS so I can look at the output?

2
sql-serverstored-proceduresreporting-services
If you look at the documentation you can see that the SP has a name, some parameters and then AS and then after that the working SQL which does whatever the SP does. Optionally there can be a begin and and end. Just take the working SQL out and play with it. docs.microsoft.com/en-us/sql/t-sql/statements/… - Dale K
In SSRS, instead of Text for the for your Query Type (for Data Set Properties), use Stored Procedure. Provided you are connected to the correct data source, you should be able to browse the procedures you have access to.. or simply enter the procedure name. click OK. if the SP needs any parameters, it will populate the parameters on the report automatically.. only you will know data types etc.. everything else is the same as a text based query.. In SSMS simply go exec name_of_stored_procedure param1,param2, paramx the parameters should match the declared types (int, varchar, datetime etc) - Harry
alternatively.. browse to the database.. then expand programmability -> stored Procedures then look for your stored procedure.. right click.. modify.. provided you have the correct permissions.. you should see the whole procedure with theSQL code in it - Harry

2 Answers

0
votes

The stored procedure [after the Use ... Set ANSI NULLS On] starts with ALTER PROCEDURE ...

That's the Alter Procedure script. Use this to edit a stored procedure.

In other words, edit the SQL code you want to optimize, then run the whole script to save the changes.

How do I work with this stored procedure in SSMS so I can look at the output?

In SSMS use the syntax for stored procedures:

EXEC myspname paramter1, parameter2, param...

Where parameter1, parameter2, etc. are the parameters described in the ALTER Procedure script, directly after the ALTER PROCEDURE myspname. Parameters are preceded by the @ symbol.

As you type-in the EXEC procedure command pop-up hints should appear describing the parameter.

Without knowing the code to the stored procedure, it could be doing any number of things based on what is passed to it by parameter. A stored procedure can do DDL and DML queries, and does not necessarily have to select anything at all for output.

0
votes

If you just want to execute the procedure in SSMS, locate it in the object browser ([DatabaseName]/Programmability/Stored Procedures). RIght-click the procedure and select 'Execute Stored Procedure'

enter image description here

Fill in the parameters and click OK and a script will be generated to run the procedure.

enter image description here

It's a bit overkill but at least everything is there and you can run it whenever you like.

If you want to edit the proc, right-click and choose modify, a new script will be created (the ALTER PROCEDURE script you mentioned). Make changes as required, run the script and that will modify the procedure, then execute the procedure to see the results.

Of course it would be safer to make a copy and edit that, you can also just run the body of the stored proc by commenting out the ALTER PROCEDURE statement until you are happy with it but you may have to declare and variables that are normally passed in as parameters.