Azure SQL Database T-SQL Stored Procedures Reference

Transact-SQL Stored Procedures

This section provides a comprehensive reference for Transact-SQL (T-SQL) stored procedures available in Azure SQL Database. Stored procedures are a set of T-SQL statements grouped together to perform a specific task. They offer benefits such as improved performance, reusability, and enhanced security.

System Stored Procedures

Azure SQL Database provides a rich set of system stored procedures for managing and querying database objects and server configurations. These procedures are typically prefixed with sp_.

sp_help - Returns information about a database object.
sp_configure - Reports or changes current configuration options.
sp_columns - Returns information about the columns of a specified table.
sp_tables - Returns information about the tables in the database.
sp_who2 - Reports information about current users and processes.
sp_lock - Reports information about the locks currently held by processes.

`sp_help`

Returns information about a database object, such as a table, view, or stored procedure.

EXEC sp_help 'TableName';

Parameters:

Parameter	Description
`@objname`	The name of the object for which to return information.

`sp_configure`

Reports or changes current configuration options for the server. Use with caution.

-- To view all configuration options:
EXEC sp_configure;

-- To change a configuration option (requires ALTER SETTINGS permission):
-- EXEC sp_configure 'show advanced options', 1;
-- RECONFIGURE;
-- EXEC sp_configure 'max worker threads', 128;
-- RECONFIGURE;
                

Parameters:

Parameter	Description
`@configname`	The name of the configuration option to change.
`@configvalue`	The new value for the configuration option.

`sp_columns`

Returns information about the columns of a specified table.

EXEC sp_columns 'YourTableName';

Parameters:

Parameter	Description
`@table_name`	The name of the table for which to return column information.

`sp_tables`

Returns information about the tables and views in the database.

EXEC sp_tables;

`sp_who2`

Reports information about current users and processes. Provides more detailed information than sp_who.

EXEC sp_who2;

`sp_lock`

Reports information about the locks currently held by processes.

EXEC sp_lock;

User-Defined Stored Procedures

You can create your own stored procedures to encapsulate custom logic for your Azure SQL Database. This promotes modularity and maintainability of your database code.

Creating a Stored Procedure:

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
                

Executing a Stored Procedure:

EXEC GetCustomerOrders @CustomerID = 101;
-- Or
EXEC GetCustomerOrders 101;
                

Modifying a Stored Procedure:

ALTER PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount, Status
    FROM Orders
    WHERE CustomerID = @CustomerID AND Status = 'Shipped';
END;
                

Deleting a Stored Procedure:

DROP PROCEDURE GetCustomerOrders;

Common Tasks and Examples

Retrieving data with output parameters:

CREATE PROCEDURE GetCustomerCount (@CustomerCount INT OUTPUT)
AS
BEGIN
    SELECT @CustomerCount = COUNT(*)
    FROM Customers;
END;

DECLARE @count INT;
EXEC GetCustomerCount @CustomerCount = @count OUTPUT;
SELECT @count AS TotalCustomers;
                

Conditional logic within stored procedures:

CREATE PROCEDURE AddProductIfAvailable (@ProductName VARCHAR(100), @Stock INT)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Products WHERE ProductName = @ProductName)
    BEGIN
        PRINT 'Product already exists.';
    END
    ELSE
    BEGIN
        INSERT INTO Products (ProductName, Stock) VALUES (@ProductName, @Stock);
        PRINT 'Product added successfully.';
    END
END;

EXEC AddProductIfAvailable 'New Gadget', 50;
                