MSDN Documentation > SQL Server > Relational Databases > Stored Procedures

In This Section

Related Topics

Executing Stored Procedures

Stored procedures offer a powerful way to encapsulate and execute SQL statements on your SQL Server instance. They can improve performance, enhance security, and simplify complex operations.

Introduction to Execution

Executing a stored procedure is akin to calling a function. You invoke the procedure, and it performs a set of predefined actions. This section details the various methods and considerations when executing stored procedures.

Basic Execution

The simplest way to execute a stored procedure is using the EXECUTE or EXEC keyword followed by the procedure name.

EXECUTE dbo.MySimpleProcedure;
EXEC MyOtherProcedure;

In these examples, dbo.MySimpleProcedure and MyOtherProcedure are names of stored procedures. If the procedure is not in the default schema for the current user, you must qualify it with the schema name.

Executing with Parameters

Most stored procedures accept parameters, which allow you to pass data into the procedure. Parameters can be specified by position or by name.

Executing by Position

When providing parameters by position, you must list them in the order they are defined in the stored procedure. If you need to skip an optional parameter, you must still provide a placeholder (e.g., NULL).

EXEC MyProcedureWithParams 'some_value', 123, 'another_value';

Executing by Name

Executing by name is generally preferred as it makes your code more readable and less prone to errors if the order of parameters changes.

EXEC MyProcedureWithParams
    @Param1 = 'some_value',
    @Param2 = 123,
    @Param3 = 'another_value';

Return Values

Stored procedures can return an integer status code to indicate success or failure. By default, a return value of 0 indicates success.

To capture the return value, you use the RETURN statement within the procedure and then capture it using a variable when executing.

DECLARE @return_status INT;
EXEC @return_status = dbo.MyProcedureThatReturnsStatus;
SELECT @return_status AS ReturnStatus;

Output Parameters

Output parameters allow a stored procedure to return data back to the caller. When defining the procedure, parameters are marked with the OUTPUT keyword. When executing, you must declare variables to receive these output values.

DECLARE @output_data VARCHAR(100);
EXEC dbo.MyProcedureWithOutput
    @InputParam = 'some input',
    @OutputParam = @output_data OUTPUT;
SELECT @output_data AS ProcedureOutput;

Executing Dynamic SQL

Sometimes, the SQL statement to be executed is not known until runtime. This can be achieved using sp_executesql, which is more secure and efficient than the older EXEC() function, especially when dealing with parameters.

DECLARE @SQLCommand NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @MyValue INT = 10;

SET @SQLCommand =
    N'SELECT column1, column2 FROM MyTable WHERE column3 = @MyParam;';
SET @ParmDefinition =
    N'@MyParam INT';

EXECUTE sp_executesql @SQLCommand, @ParmDefinition, @MyParam = @MyValue;

Using sp_executesql with parameters helps prevent SQL injection attacks.

Permissions

To execute a stored procedure, a user must have the EXECUTE permission on that procedure. This permission can be granted or denied on a per-user or per-role basis.

GRANT EXECUTE ON dbo.MyProcedure TO MyUser;

Further Reading