Dynamic SQL Server stored procedure execution form in SSMS

By: Greg Robidoux | Comments (1) | Related: > SQL Server Management Studio

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.

Execut1

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.

Execut2

Enter the value you want to use for the parameter and select "OK" to run the stored procedure.

Execut3

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.

Execut4

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

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, June 17, 2013 - 3:25:10 PM - Dean-O

I am new to dynamic SQL and I have the following from my sql profiler.

my understanding was the syntax was

exec Stored_Procedure Parameters

I don't understand what the bold text below is. They do not look like paramaters being sent into the stored procedure, but rather results coming back from the stored procedure. The rest of the items in the list look like parameters being passed into the stored procedure.

How far off am I?

declare @p1 int
set @p1=1
declare @p2 nvarchar(255)
set @p2=N'ng_add_sig_events: (Success), Sig Event Added.'
exec ng_add_sig_events @po_result_code=@p1 output,@po_result_message=@p2 output,@pi_practice_id=N'0011',@pi_enterprise_id=N'00021',@pi_sig_event_id='A9D57824-638',@pi_source1_id='557D78F4C',@pi_source2_id=NULL,@pi_source3_id=NULL,@pi_source4_id=NULL,@pi_event_source_type=N'4',@pi_sig_id=N'38',@pi_sig_msg=N'Employer Added',@pi_pre_mod=N'<none>',@pi_post_mod=N'Foo',@pi_user_id=154,@pi_group_id=NULL,@pi_check_sig_admin_ind=N'N',@pi_create_timestamp_tz=0
select @p1, @p2