Write Stored Procedure In Sql Server

Stored procedures are the backbone of dependable, secure, and high-performance database applications in SQL Server. Unlike ad-hoc queries sent from an application, a stored procedure resides on the database server, allowing the engine to optimize execution plans, reduce network traffic, and enforce business logic centrally. That's why they act as a pre-compiled collection of Transact-SQL statements stored under a name and processed as a unit. Mastering the art of writing them is essential for any developer or database administrator aiming to build scalable data solutions That's the whole idea..

Understanding the Core Syntax

Before diving into complex logic, it is vital to grasp the fundamental structure. Every stored procedure begins with a CREATE PROCEDURE (or CREATE PROC) statement, followed by the procedure name, an optional parameter list, and the procedural body wrapped in a BEGIN...END block.

CREATE PROCEDURE [schema_name.]procedure_name
    [ @parameter_name data_type [ = default_value ] [ OUTPUT ] ] [ ,...n ]
[ WITH  [ ,...n ] ]
[ FOR REPLICATION ]
AS
BEGIN
    SET NOCOUNT ON;
    -- Procedure logic goes here
    SELECT column_list FROM table_name WHERE condition;
    RETURN integer_value;
END;
GO

A few critical components deserve immediate attention. The RETURN statement is strictly for integer status codes (typically 0 for success, non-zero for errors), not for returning data sets. The SET NOCOUNT ON statement prevents the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the result set. This significantly reduces network overhead, especially in procedures with multiple loops or frequent DML operations. Data is returned via SELECT statements or OUTPUT parameters.

Designing Effective Parameters

Parameters make stored procedures dynamic and reusable. They function similarly to variables in programming languages but are defined in the procedure header. SQL Server supports two primary parameter modes: Input and Output.

Input parameters pass values into the procedure. You can assign default values, making the parameter optional during execution. If a default is provided, the caller can omit the parameter entirely or use the DEFAULT keyword.

Output parameters pass values back to the caller. This is the standard mechanism for returning scalar values (like a newly generated ID, a calculated total, or a status flag) alongside result sets. The caller must declare a variable and pass it using the OUTPUT keyword during execution.

Consider this example demonstrating both types:

CREATE PROCEDURE Sales.GetOrderSummary
    @CustomerID INT,
    @StartDate DATE = '2020-01-01', -- Optional parameter with default
    @TotalOrders INT OUTPUT,        -- Output parameter
    @TotalRevenue MONEY OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalOrders = COUNT(*),
           @TotalRevenue = SUM(TotalAmount)
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
      AND OrderDate >= @StartDate;

    -- Return detail rows
    SELECT OrderID, OrderDate, TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
      AND OrderDate >= @StartDate
    ORDER BY OrderDate DESC;

    RETURN 0; -- Success code
END;
GO

To execute this, the calling code must declare variables to capture the output:

DECLARE @OrderCount INT, @Revenue MONEY;
EXEC Sales.GetOrderSummary 
    @CustomerID = 42, 
    @StartDate = '2023-01-01', 
    @TotalOrders = @OrderCount OUTPUT, 
    @TotalRevenue = @Revenue OUTPUT;

PRINT 'Orders: ' + CAST(@OrderCount AS VARCHAR(10));
PRINT 'Revenue: ' + CAST(@Revenue AS VARCHAR(20));

Implementing Control Flow and Logic

The real power of stored procedures lies in procedural logic. Transact-SQL provides standard control-of-flow keywords: IF...ELSE, WHILE, CASE, BEGIN...END, and TRY...CATCH.

Conditional Logic allows branching execution paths. A common pattern is "Upsert" logic (Update or Insert), where you check for existence before deciding the action It's one of those things that adds up..

IF EXISTS (SELECT 1 FROM dbo.Products WHERE ProductID = @ProductID)
BEGIN
    UPDATE dbo.Products SET Price = @NewPrice WHERE ProductID = @ProductID;
    SELECT 'Updated' AS Action;
END
ELSE
BEGIN
    INSERT INTO dbo.Products (ProductID, Price) VALUES (@ProductID, @NewPrice);
    SELECT 'Inserted' AS Action;
END

Error Handling is non-negotiable in production code. The TRY...CATCH construct (introduced in SQL Server 2005) is the standard mechanism. Inside a CATCH block, you can access system functions like ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), and ERROR_LINE() to log details or re-throw the error using THROW Most people skip this — try not to..

BEGIN TRY
    BEGIN TRANSACTION;
    
    UPDATE Accounts SET Balance -= @Amount WHERE AccountID = @FromAccount;
    UPDATE Accounts SET Balance += @Amount WHERE AccountID = @ToAccount;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    
    -- Log error to a central table
    INSERT INTO dbo.ErrorLog (ErrorTime, ErrorNumber, ErrorMessage, ProcedureName)
    VALUES (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE());
    
    THROW; -- Re-throw to caller
END CATCH

Transaction Management Best Practices

Stored procedures are the ideal boundary for database transactions. On top of that, the golden rule is: **Keep transactions as short as possible. ** Long-running transactions hold locks, blocking other users and increasing deadlock potential.

Always check @@TRANCOUNT before starting a transaction. Now, if the procedure is called from within an existing transaction (nested transaction), you generally should not commit or rollback the outer transaction, but you must handle errors gracefully. The pattern shown in the TRY...CATCH block above—checking @@TRANCOUNT > 0 before rolling back—is the safest approach Not complicated — just consistent..

Avoid explicit transaction management for simple SELECT statements. Use READ COMMITTED SNAPSHOT ISOLATION (RCSI) at the database level to reduce blocking for read-heavy workloads without requiring explicit locking hints in your procedures It's one of those things that adds up. Surprisingly effective..

Performance Optimization Techniques

Writing a procedure that works is only half the battle; writing one that scales requires attention to execution plans and parameter sniffing.

Parameter Sniffing

SQL Server creates an execution plan based on the parameter values used during the first execution (compilation). If subsequent executions use vastly different values (e.g., first call returns 10 rows, second returns 1 million), the cached plan may be disastrously inefficient for the second call.

Mitigation Strategies:

1. OPTION (RECOMPILE): Forces a fresh plan on every execution. Use sparingly for high-frequency procedures due to CPU overhead.
2. OPTIMIZE FOR (@Param = 'TypicalValue'): Instructs the optimizer to build a plan for a specific "average" value.
3. Local Variables: Assign input parameters to local variables inside the procedure. The optimizer uses density vector averages rather than sniffed values.

   CREATE PROCEDURE GetOrders @CustomerID INT
   AS
   BEGIN
       DECLARE @LocalCustID INT = @CustomerID; -- Mask the parameter
       SELECT * FROM Orders WHERE CustomerID = @LocalCustID;
   END
   

Avoiding Common Anti-Patterns