T-SQL Stored Procedures

Stored procedures are precompiled collections of one or more T-SQL statements that are stored on the database server. They offer significant benefits in terms of performance, security, and reusability.

What are Stored Procedures?

Think of a stored procedure as a function or a subroutine in programming languages. You define it once, give it a name, and then you can execute it multiple times by simply calling its name. This eliminates the need to repeatedly send the same SQL code to the server, reducing network traffic and improving execution speed as the query plan is cached.

Benefits of Stored Procedures

Performance: Compiled once, executed many times.
Reusability: Write code once, call it from multiple applications or other procedures.
Security: Grant execute permissions to users without granting direct table access, reducing the risk of data breaches.
Maintainability: Centralize business logic in the database. Changes only need to be made in one place.
Reduced Network Traffic: Instead of sending long SQL statements, you send a single EXECUTE command.
Encapsulation: Hide complex SQL logic behind a simple interface.

Creating a Stored Procedure

The basic syntax for creating a stored procedure is:

CREATE PROCEDURE schema_name.procedure_name
                [ @parameter1 datatype [ = default_value ],
                  @parameter2 datatype [ = default_value ],
                  ... ]
            AS
            BEGIN
                -- T-SQL statements here
                -- Use parameters if defined
                SELECT 'Hello, ' + @parameter1 AS Greeting;
            END;

Key components:

CREATE PROCEDURE: Keywords to start the procedure definition.
schema_name.procedure_name: The name of the procedure, often prefixed with its schema (e.g., dbo.MyProcedure).
@parameterName datatype [= default_value]: Optional parameters that the procedure can accept. Parameters are prefixed with '@'.
AS: Separates the procedure signature from its body.
BEGIN...END: Encloses the T-SQL statements that form the procedure's logic.

Example: Creating a simple procedure to get customer details by ID

-- Check if the procedure already exists and drop it if it does
                IF OBJECT_ID('dbo.GetCustomerByID', 'P') IS NOT NULL
                    DROP PROCEDURE dbo.GetCustomerByID;
                GO

                CREATE PROCEDURE dbo.GetCustomerByID
                    @CustomerID INT
                AS
                BEGIN
                    -- SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client
                    -- for each statement in a stored procedure.
                    SET NOCOUNT ON;

                    SELECT CustomerID, CompanyName, ContactName, City, Country
                    FROM Sales.Customers
                    WHERE CustomerID = @CustomerID;
                END;
                GO

Executing a Stored Procedure

You execute a stored procedure using the EXECUTE or EXEC command:

EXECUTE schema_name.procedure_name @parameter1 = value1, @parameter2 = value2;
            -- or
            EXEC schema_name.procedure_name @parameter1 = value1, @parameter2 = value2;

Example: Executing the GetCustomerByID procedure

EXECUTE dbo.GetCustomerByID @CustomerID = 5;

If a parameter has a default value, you can omit it during execution. If the order of parameters is known, you can also omit the parameter names, but this is generally discouraged for clarity and maintainability:

-- Using default parameter value (if defined)
            EXEC dbo.GetCustomerByID;

            -- Executing by position (less recommended)
            EXEC dbo.GetCustomerByID 10;

Modifying a Stored Procedure

To modify an existing stored procedure, you use the ALTER PROCEDURE statement. The syntax is similar to CREATE PROCEDURE.

ALTER PROCEDURE schema_name.procedure_name
                [ @parameter1 datatype [ = default_value ],
                  ... ]
            AS
            BEGIN
                -- New or modified T-SQL statements
            END;
            GO

Important: When you use ALTER PROCEDURE, you replace the entire existing procedure definition with the new one.

Dropping a Stored Procedure

To remove a stored procedure from the database, use the DROP PROCEDURE statement:

DROP PROCEDURE schema_name.procedure_name;
            GO

It's good practice to check if the procedure exists before dropping it to avoid errors:

IF OBJECT_ID('dbo.OldProcedure', 'P') IS NOT NULL
                DROP PROCEDURE dbo.OldProcedure;
            GO

Input and Output Parameters

Stored procedures can have input parameters (the default), output parameters, and even return values.

Output Parameters

Output parameters allow a procedure to return a value back to the caller, similar to output parameters in other programming languages.

Example: Procedure with an output parameter

IF OBJECT_ID('dbo.GetCustomerCountByCity', 'P') IS NOT NULL
                    DROP PROCEDURE dbo.GetCustomerCountByCity;
                GO

                CREATE PROCEDURE dbo.GetCustomerCountByCity
                    @CityName NVARCHAR(50),
                    @CustomerCount INT OUTPUT
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT @CustomerCount = COUNT(*)
                    FROM Sales.Customers
                    WHERE City = @CityName;
                END;
                GO

Executing this procedure with an output parameter:

DECLARE @Count INT;
            EXEC dbo.GetCustomerCountByCity @CityName = 'London', @CustomerCount = @Count OUTPUT;
            SELECT 'Number of customers in London:', @Count AS ResultCount;

Return Values

Stored procedures can also return an integer status code using the RETURN statement. This is typically used to indicate success (usually 0) or failure (a non-zero value).

Example: Procedure returning a status code

IF OBJECT_ID('dbo.AddProduct', 'P') IS NOT NULL
                    DROP PROCEDURE dbo.AddProduct;
                GO

                CREATE PROCEDURE dbo.AddProduct
                    @ProductName NVARCHAR(100),
                    @Price DECIMAL(10, 2)
                AS
                BEGIN
                    SET NOCOUNT ON;

                    IF EXISTS (SELECT 1 FROM Production.Products WHERE ProductName = @ProductName)
                    BEGIN
                        -- Product already exists, return an error code
                        RETURN 1; -- Indicate failure
                    END
                    ELSE
                    BEGIN
                        INSERT INTO Production.Products (ProductName, UnitPrice)
                        VALUES (@ProductName, @Price);
                        RETURN 0; -- Indicate success
                    END
                END;
                GO

Executing and checking the return value:

DECLARE @ReturnCode INT;
            EXEC @ReturnCode = dbo.AddProduct @ProductName = 'New Gadget', @Price = 199.99;

            IF @ReturnCode = 0
                SELECT 'Product added successfully.';
            ELSE
                SELECT 'Failed to add product. It may already exist.';

Tip: Use RETURN for status codes and OUTPUT parameters for returning data sets or multiple values.

System Stored Procedures

SQL Server also provides numerous built-in system stored procedures, usually prefixed with sp_ (e.g., sp_help, sp_configure), that you can use to manage and query the server and its databases.

Example:

EXEC sp_help 'Sales.Customers';

While you can create your own procedures starting with sp_, it's strongly discouraged as it can conflict with future system procedures and can lead to performance issues due to how the database resolves names.