MSDN Documentation: SQL Server Stored Procedures Samples

Explore a collection of practical and illustrative samples for SQL Server stored procedures. These examples cover various functionalities, from basic CRUD operations to more complex business logic and data manipulation.

Featured Samples

Customer Management Stored Procedure

SQL SQL Server Stored Procedure CRUD

A comprehensive set of stored procedures to manage customer data, including adding new customers, retrieving customer information, updating customer details, and deleting customers.


-- Example: Add New Customer
CREATE PROCEDURE usp_AddCustomer
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Email VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Customers (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
    SELECT SCOPE_IDENTITY() AS CustomerID;
END;

View Full Example

Order Processing Stored Procedure

SQL SQL Server Stored Procedure Transactions E-commerce

Demonstrates a stored procedure for processing customer orders, ensuring data integrity through transactions and handling inventory updates.


-- Example: Create Order
CREATE PROCEDURE usp_CreateOrder
    @CustomerID INT,
    @OrderDate DATETIME = GETDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @OrderID INT;
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Orders (CustomerID, OrderDate)
        VALUES (@CustomerID, @OrderDate);
        SET @OrderID = SCOPE_IDENTITY();
        -- Further logic to add order items and update inventory
        COMMIT TRANSACTION;
        SELECT @OrderID AS OrderID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

View Full Example

Product Inventory Update

SQL SQL Server Stored Procedure Inventory Performance

An efficient stored procedure designed to update product inventory levels, often used in conjunction with sales or stock receipts.


-- Example: Update Stock Quantity
CREATE PROCEDURE usp_UpdateProductStock
    @ProductID INT,
    @QuantityChange INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Products
    SET StockQuantity = StockQuantity + @QuantityChange
    WHERE ProductID = @ProductID;
END;

View Full Example

Parameterized Reporting Procedure

SQL SQL Server Stored Procedure Reporting Parameters

A stored procedure that accepts parameters to generate customized reports, such as sales figures within a specific date range.


-- Example: Sales Report by Date Range
CREATE PROCEDURE usp_GetSalesReportByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        o.OrderID,
        c.FirstName + ' ' + c.LastName AS CustomerName,
        o.OrderDate,
        SUM(oi.Quantity * oi.UnitPrice) AS TotalAmount
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN OrderItems oi ON o.OrderID = oi.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY o.OrderID, c.FirstName, c.LastName, o.OrderDate
    ORDER BY o.OrderDate;
END;

View Full Example

More Resources

SQL Server Documentation MSDN Documentation Home