How to write a store procedure?

Owned by Faisal AL Mahmud
Last updated: Jan 06, 2020 by Maksud Ul Alam
1 min read

This article is a convention for writing store procedure and integration with back-end code. Everybody is requested to follow the following coding conventions on this project alongside regular conventions.

Instructions

  1. A stored procedure must have a drop create option. To prevent Parameter Sniffing don't use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries. i.e:
    DROP PROCEDURE IF EXISTS {YOUR_STORE_PROCEDURE}
    GO

    CREATE PROCEDURE {YOUR_STORE_PROCEDURE}
    @Param INT
    AS
    DECLARE @TempParam INT
    SET @TempParam = @Param
    BEGIN
    SELECT *
    FROM Customers e WHERE
    CustomerId = @TempParam
    END
    GO

  2. In C# code make sure you have close the DbConnection after SP calling. For example:
    try
    {
    EnsureConnectionOpen();

    //your SP calling code
    }
    catch (Exception ex)
    {
    //your exception handling plan
    }
    finally
    {
    CloseDbConnection();
    }

  3. To avoid UnitOfWork related exception please use
    [UnitOfWork(isTransactional: false)] attribute above the SP calling method (Public ).