Dynamic SQL Server stored procedure execution form in SSMS

By:   |   Updated: 2007-11-16   |   Comments (1)   |   Related: > SQL Server Management Studio

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?

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


get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Article Last Updated: 2007-11-16

Comments For This Article

Monday, June 17, 2013 - 3:25:10 PM - Dean-O Back To Top (25459)


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 @[email protected] output,@[email protected] 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

get free sql tips
agree to terms