Executing a Stored Procedure With Parameters using SSDT
Mon, Jul 29 2013 14:11
As detailed in this prior post, you can execute SQL Server stored procedures using the SQL Server Object Explorer that is part of the SQL Server Data Tools (SSDT). But in that example, the stored procedure had no parameters. This post goes one step further to show how to call stored procedures with parameters.
See this link for an introduction to SQL Server Object Explorer.
To execute a stored procedure with parameters from a SQL Server database using SQL Server Object Explorer:
1) Open the SQL Server Object Explorer toolbox using View | SQL Server Object Explorer from Visual Studio.
2) Drill down to your database, then the Programmability node, then to Stored Procedures.
3) Right-click on the stored procedure to execute and select Execute Procedure…
4) If the stored procedure has parameters, a dialog is displayed for entry of the parameters:
5) Fill in the Value column and click OK.
6) The generated T-SQL is displayed in a Query pane, the query is executed automatically and the return values is displayed in the Results pane.
You can edit the generated T-SQL directly and click the Execute button (first button in the Query window toolbar) to execute the T-SQL again with different values.
Notice that you can enter the strings into the Value column and they are quoted automatically when the T-SQL is generated. The same is NOT true of dates:
For dates, you need to put in the quotes yourself, either in the Value column or after the T-SQL is generated.
Use the SQL Server Object Explorer any time you want to execute a SQL Server stored procedure as part of your development or debugging efforts.