Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Dynamic SQL Server stored procedure execution form in SSMS


By:   |   Updated: 2007-11-16   |   Comments (1)   |   Related: More > 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.

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

 



Last Updated: 2007-11-16


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
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

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


Learn more about SQL Server tools