Executing Stored Procedures

SQL Server Documentation > Database Engine > Stored Procedures > Executing Stored Procedures

Stored procedures are precompiled SQL statements stored on the database server. They offer numerous benefits, including improved performance, enhanced security, and code reusability. This section covers the various methods for executing stored procedures in SQL Server.

Note: Ensure you have the necessary permissions to execute the target stored procedure.

Methods of Execution

Stored procedures can be executed using the EXECUTE or EXEC Transact-SQL statements.

1. Executing a Stored Procedure without Parameters

To execute a stored procedure that does not require any input parameters, simply use the EXECUTE keyword followed by the procedure name.

Syntax

EXECUTE procedure_name;

Example

EXECUTE dbo.usp_GetAllProducts;

2. Executing a Stored Procedure with Parameters

When a stored procedure requires parameters, you can pass them positionally or by name.

a) Positional Parameter Passing

Parameters are passed in the order they are defined in the stored procedure.

Syntax

EXECUTE procedure_name parameter1_value, parameter2_value, ...;

Example

Assume a procedure dbo.usp_GetProductByID takes one parameter: @ProductID INT.

EXECUTE dbo.usp_GetProductByID 101;

b) Named Parameter Passing

This is the recommended approach as it improves readability and is not dependent on the order of parameters. You explicitly specify the parameter name and its value.

Syntax

EXECUTE procedure_name @parameter1_name = parameter1_value, @parameter2_name = parameter2_value, ...;

Example

Using the same dbo.usp_GetProductByID procedure:

EXECUTE dbo.usp_GetProductByID @ProductID = 101;

You can mix positional and named parameters, but positional parameters must come first.

-- Valid (but generally avoid mixing unless necessary)
EXECUTE dbo.usp_GetProductByID 101, @OutputParameter = 'SomeValue';

3. Storing Results of a Stored Procedure

You can store the results returned by a stored procedure into variables or tables.

a) Storing Scalar Results

Use the SET or SELECT statement to capture a single value returned by a stored procedure.

Example

DECLARE @ProductCount INT;
EXECUTE @ProductCount = dbo.usp_GetProductCount;
SELECT @ProductCount AS NumberOfProducts;

b) Storing Result Sets

Use the INSERT...EXEC statement to populate a temporary table or table variable with the result set of a stored procedure.

Example

-- Using a table variable
DECLARE @ProductDetails TABLE (
    ProductID INT,
    ProductName VARCHAR(100),
    UnitPrice MONEY
);

INSERT INTO @ProductDetails (ProductID, ProductName, UnitPrice)
EXECUTE dbo.usp_GetProductDetails @CategoryID = 5;

SELECT * FROM @ProductDetails;

-- Using a temporary table
CREATE TABLE #TempProductDetails (
    ProductID INT,
    ProductName VARCHAR(100),
    UnitPrice MONEY
);

INSERT INTO #TempProductDetails
EXECUTE dbo.usp_GetProductDetails @CategoryID = 5;

SELECT * FROM #TempProductDetails;
DROP TABLE #TempProductDetails; -- Clean up

4. Executing Stored Procedures from Other Stored Procedures

You can call one stored procedure from within another. This promotes modularity and code reuse.

Example

CREATE PROCEDURE dbo.usp_ProcessOrder (@OrderID INT)
AS
BEGIN
    -- Execute another procedure to validate the order
    EXECUTE dbo.usp_ValidateOrder @OrderID = @OrderID;

    -- Further processing...
    PRINT 'Order ' + CAST(@OrderID AS VARCHAR) + ' processed successfully.';
END;
Tip: Using EXECUTE (string_variable) allows you to dynamically build and execute SQL statements, including stored procedure calls. However, be cautious of SQL injection vulnerabilities when constructing dynamic SQL.

Common Execution Scenarios

Retrieving Data

Execute a stored procedure to fetch specific data based on criteria.

-- Get customers from a specific city
EXECUTE dbo.usp_GetCustomersByCity @City = 'London';

Modifying Data

Execute stored procedures for insert, update, or delete operations.

-- Add a new employee
EXECUTE dbo.usp_AddEmployee
    @FirstName = 'Jane',
    @LastName = 'Doe',
    @DepartmentID = 3,
    @HireDate = '2023-10-27';

Executing with Output Parameters

Procedures can return values through output parameters.

DECLARE @CustomerCount INT;
EXECUTE dbo.usp_GetCustomerCountByCountry
    @Country = 'USA',
    @CustomerCount = @CustomerCount OUTPUT;

SELECT @CustomerCount AS NumberOfCustomersInUSA;