Stored Procedures & Functions

Stored Procedure

CREATE PROCEDURE sp_GetProductsByCategory
    @CategoryId INT,
    @MinPrice DECIMAL(18,2) = 0     -- optional
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Price, Stock
    FROM Products
    WHERE CategoryId = @CategoryId
      AND Price >= @MinPrice
    ORDER BY Price;
END;
GO

-- Execute
EXEC sp_GetProductsByCategory @CategoryId = 5;
EXEC sp_GetProductsByCategory @CategoryId = 5, @MinPrice = 100;

Output Parameter

CREATE PROCEDURE sp_CreateUser
    @Name NVARCHAR(100),
    @Email NVARCHAR(150),
    @NewId INT OUTPUT
AS
BEGIN
    INSERT INTO Users (Name, Email) VALUES (@Name, @Email);
    SET @NewId = SCOPE_IDENTITY();
END;

-- Execute
DECLARE @Id INT;
EXEC sp_CreateUser 'Rahim', 'r@mail.com', @Id OUTPUT;
SELECT @Id;

Return Value

CREATE PROCEDURE sp_DeleteProduct @Id INT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Products WHERE Id = @Id)
        RETURN -1;

    DELETE FROM Products WHERE Id = @Id;
    RETURN 0;
END;

DECLARE @Result INT;
EXEC @Result = sp_DeleteProduct 5;

Control Flow

CREATE PROCEDURE sp_TransferStock
    @FromId INT, @ToId INT, @Quantity INT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @Stock INT;
        SELECT @Stock = Stock FROM Products WHERE Id = @FromId;

        IF @Stock < @Quantity BEGIN
            THROW 50001, 'Insufficient stock', 1;
        END;

        UPDATE Products SET Stock = Stock - @Quantity WHERE Id = @FromId;
        UPDATE Products SET Stock = Stock + @Quantity WHERE Id = @ToId;

        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END;

Scalar Function

CREATE FUNCTION fn_TotalOrderValue (@OrderId INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2);
    SELECT @Total = SUM(Price * Quantity)
    FROM OrderItems WHERE OrderId = @OrderId;
    RETURN ISNULL(@Total, 0);
END;

-- Use
SELECT Id, dbo.fn_TotalOrderValue(Id) AS Total FROM Orders;

Table-valued Function

CREATE FUNCTION fn_ProductsByCategory (@CatId INT)
RETURNS TABLE
AS
RETURN (
    SELECT Id, Name, Price FROM Products WHERE CategoryId = @CatId
);

-- Use
SELECT * FROM dbo.fn_ProductsByCategory(5);

-- Multi-statement
CREATE FUNCTION fn_TopProducts (@N INT)
RETURNS @T TABLE (Id INT, Name NVARCHAR(200), Sales INT)
AS
BEGIN
    INSERT INTO @T
    SELECT TOP(@N) p.Id, p.Name, SUM(oi.Quantity)
    FROM Products p
    INNER JOIN OrderItems oi ON oi.ProductId = p.Id
    GROUP BY p.Id, p.Name
    ORDER BY SUM(oi.Quantity) DESC;
    RETURN;
END;

Calling SP from EF Core

// Raw SQL
var products = await _db.Products
    .FromSqlRaw("EXEC sp_GetProductsByCategory @p0", 5)
    .ToListAsync();

// SqlParameter (safer)
var param = new SqlParameter("@CategoryId", 5);
var products = await _db.Products
    .FromSqlRaw("EXEC sp_GetProductsByCategory @CategoryId", param)
    .ToListAsync();

// ExecuteSqlRaw — non-query
await _db.Database.ExecuteSqlRawAsync(
    "EXEC sp_TransferStock @p0, @p1, @p2", 1, 2, 10);

Calling SP from Dapper

using var db = new SqlConnection(_conn);
var products = await db.QueryAsync<Product>(
    "sp_GetProductsByCategory",
    new { CategoryId = 5 },
    commandType: CommandType.StoredProcedure);

SP vs Query — কখন কী?

• SP — repeated complex logic, batch ops, encapsulation
• Query/EF — simple CRUD, dynamic queries