Why should you care?

APIs call dbo.usp_GetCustomerOrders instead of shipping ad-hoc SQL strings — reuse, plan cache, and tighter security.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, City, Amount, OrderDate
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderId DESC;
END
GO
EXEC dbo.usp_GetCustomerOrders @CustomerId = 1;

What happened?

• CREATE OR ALTER upserts the procedure.
• SET NOCOUNT ON reduces done messages.
• EXEC runs it for customer 1.

Practice next

1. Create the procedure and execute it.
2. Grant EXECUTE to a test user later in security lessons.
3. Add an optional @Top INT parameter with TOP (@Top).
4. Return a second result set with customer name.
5. Add TRY/CATCH and THROW on bad @CustomerId.

Remember

Procedures package reusable T-SQL. Call with EXEC and parameters. Keep one clear responsibility per proc.

Order history endpoint

ASP.NET Core calls DataVerse usp_GetCustomerOrders.

Outcome: One contract for web and mobile history.