Executing SQL Stored Procedures

This document covers the essential methods and syntax for executing stored procedures in SQL Server.

Overview of Execution

Stored procedures are precompiled SQL statements that can be executed repeatedly. Executing a stored procedure is typically done using the EXECUTE or EXEC command, followed by the procedure name and any necessary parameters.

Basic Execution

The simplest way to execute a stored procedure is by providing its name. If the procedure does not require any parameters, the syntax is straightforward:

EXECUTE dbo.MySimpleProcedure;

Alternatively, the shorter alias EXEC can be used:

EXEC dbo.MySimpleProcedure;

Executing Procedures with Parameters

Most stored procedures accept input parameters, output parameters, or both. Parameters are passed in a specific order or by name.

Passing Parameters by Position

If you know the exact order of parameters, you can pass them sequentially:

EXECUTE dbo.UpdateCustomerInfo
    101,           -- CustomerID (First parameter)
    'Jane Doe',    -- NewCustomerName (Second parameter)
    'jane.doe@example.com'; -- NewEmail (Third parameter)

Passing Parameters by Name

Passing parameters by name is generally recommended as it makes the code more readable and less prone to errors if the procedure's parameter order changes in the future.

EXECUTE dbo.UpdateCustomerInfo
    @CustomerID = 101,
    @NewCustomerName = 'Jane Doe',
    @NewEmail = 'jane.doe@example.com';

Mixing Positional and Named Parameters

You can mix positional and named parameters, but once you use a named parameter, all subsequent parameters must also be named.

EXECUTE dbo.UpdateCustomerInfo
    101, -- Positional parameter for CustomerID
    @NewCustomerName = 'Jane Doe',
    @NewEmail = 'jane.doe@example.com';
Note: The schema name (e.g., dbo) should be included if it's not the default schema for the user executing the procedure.

Executing Procedures that Return Values

Stored procedures can return values in several ways:

Handling Return Status Codes

The @@ perintah_status global variable holds the return status of the last executed procedure. A value of 0 usually indicates success.

DECLARE @return_status INT;
EXEC @return_status = dbo.PerformOperation;
IF @return_status = 0
BEGIN
    PRINT 'Operation completed successfully.';
END
ELSE
BEGIN
    PRINT 'Operation failed with status: ' + CAST(@return_status AS VARCHAR);
END;

Capturing Output Parameters

Declare variables to hold the output parameter values before execution.

Example: Stored Procedure with Output Parameter

Assume a procedure named GetCustomerNameByID exists:

CREATE PROCEDURE dbo.GetCustomerNameByID
                @CustomerID INT,
                @CustomerName VARCHAR(100) OUTPUT
            AS
            BEGIN
                SELECT @CustomerName = Name FROM Customers WHERE CustomerID = @CustomerID;
            END;

Executing it and capturing the output:

DECLARE @CustName VARCHAR(100);
EXEC dbo.GetCustomerNameByID
    @CustomerID = 101,
    @CustomerName = @CustName OUTPUT;

SELECT 'Customer Name: ' + @CustName AS Result;

Executing Procedures that Return Result Sets

Procedures that contain SELECT statements will return result sets. These can be directly displayed or inserted into temporary tables.

-- Execute and display the result set
EXEC dbo.GetRecentOrders;

-- Execute and insert into a temporary table
CREATE TABLE #TempOrders (OrderID INT, OrderDate DATE, TotalAmount DECIMAL(10, 2));
INSERT INTO #TempOrders (OrderID, OrderDate, TotalAmount)
EXEC dbo.GetRecentOrders;

SELECT * FROM #TempOrders;
DROP TABLE #TempOrders;
Tip: Use temporary tables (#TableName) to store result sets from stored procedures for further processing or analysis within your current session.

Security Considerations

When executing stored procedures, ensure that the user account has the necessary permissions on the procedure itself and the underlying objects it accesses. Consider using the EXECUTE AS clause for impersonation if required, but do so with caution.

Best Practices