Problem
The purpose for most stored procedures is for execution within applications, but there are some stored procedures that may be used for administrative purposes and only get executed ad hoc. In addition, during testing you run stored procedures interactively to make sure things are working correctly. You have the ability to run any stored procedure directly from a query window and include the necessary parameters, but is there any easier way to know what parameters a stored procedure requires and to pass the parameters directly to a stored procedure?
Solution
In SQL Server Management Studio you have the ability to execute a stored procedure directly from the object browser tree. Just browse to the desired stored procedure and right click and select “Execute Stored Procedure…” as shown below.
When you select “Execute Stored Procedure…” a window such as the following will pop up that will give you the list of parameters to use for the stored procedure.
Enter the value you want to use for the parameter and select “OK” to run the stored procedure.
The stored procedure will execute based on the parameters you pass as well as create sample code such as the following that is used for the execution of the stored procedure.
That’s all there is to it. This is a pretty simple tip, but could save you a lot of time if you need to run a stored procedure and don’t want to mess with having to type the commands or if you don’t remember the exact parameters that the stored procedure needs.
The only downside to this is that it only works for user defined stored procedures. System stored procedures do not give you the option to use the “Execute Stored Procedure…”.
Next Steps
- Next time you need to run a stored procedure use this technique as a faster way to execute the code
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
