Test a stored procedure in Microsoft Sql Server Management Studio

How can you test an existing stored procedure in Microsof开发者_如何学Got Sql Server Management Studio?

Not sure of best approach here is how I do it:

You can right click the sp > tasks > execute to > new query window. This will allow you to call the SP with parameters.

You can then do selects at various points in the SP for debugging.

The other way if it is a really complex SP is to take the code out of an SP and just declare variables in place of the parameters then you can just run the TSQL code directly.

Would love to hear any better ways though.

Here The explanation/Example from MSDN Using Variables and Parameters (Database Engine)

Transact-SQL has several ways to pass data between Transact-SQL statements. These include the following:

Transact-SQL local variables.

A Transact-SQL variable is an object in Transact-SQL batches and scripts that can hold a data value. After the variable has been declared, or defined, one statement in a batch can set the variable to a value and a later statement in the batch can get the value from the variable. For example:

Copy

USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVar int;
SET @EmpIDVar = 1234;
SELECT *
FROM HumanRresources.Employee
WHERE BusinessEntityID = @EmpIDVar;

Note The maximum number of local variables that can be declared in a batch is 10,000. Transact-SQL parameters.

A parameter is an object used to pass data between a stored procedure and the batch or script that executes the stored procedure. Parameters can be either input or output parameters. For example:

Copy

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE ParmSample @EmpIDParm int AS
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @EmpIDParm ;
GO

EXEC ParmSample @EmpIDParm = 109 ;
GO

Applications use application variables and parameter markers to work with the data from Transact-SQL statements.

Application variables

The application programming languages such as C, C++, Basic, and Java have their own variables for holding data. Applications using the database APIs must move the data returned by Transact-SQL statements into application variables before they can work with the data. This is typically done using a process called binding. The application uses an API function to bind the result set column to a program variable. When a row is fetched the API provider or driver moves the data from the column to the bound program variable.

Parameter markers

Parameter markers are supported by the ADO, OLE DB, and ODBC-based database APIs. A parameter marker is a question mark (?) placed in the location of an input expression in a Transact-SQL statement. The parameter marker is then bound to an application variable. This allows data from application variables to be used as input in Transact-SQL statements. Parameter markers also let stored procedure output parameters and return codes be bound to application variables. The output data is then returned to the bound variables when the procedure is executed. The DB-Library API also supports binding stored procedure parameter and return codes to program variables.

Regards