Bits of .NET

📦 Stored Procedures = Database Logic

Business logic in code is fine. Stored procedures move logic to database. Performance, security, consistency.

📝 Creating Procedures

-- Basic procedure
CREATE PROCEDURE GetUserById
    @UserId INT
AS
BEGIN
    SELECT * FROM Users WHERE Id = @UserId;
END;

-- Execute
EXEC GetUserById 1;

-- With OUTPUT parameter
CREATE PROCEDURE GetUserCount
    @TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*) FROM Users;
END;

-- Execute with OUTPUT
DECLARE @Count INT;
EXEC GetUserCount @Count OUTPUT;
PRINT @Count;

-- With transaction
CREATE PROCEDURE CreateOrder
    @UserId INT,
    @ProductId INT,
    @Quantity INT,
    @OrderId INT OUTPUT
AS
BEGIN
    BEGIN TRANSACTION;
    
    BEGIN TRY
        INSERT INTO Orders (UserId, OrderDate)
        VALUES (@UserId, GETDATE());
        
        SET @OrderId = SCOPE_IDENTITY();
        
        INSERT INTO OrderItems (OrderId, ProductId, Quantity)
        VALUES (@OrderId, @ProductId, @Quantity);
        
        UPDATE Products
        SET Stock = Stock - @Quantity
        WHERE Id = @ProductId;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

🎯 Procedure Benefits

# Stored Procedure Benefits
- Better performance (precompiled)
- Security (encapsulation)
- Consistency (same logic)
- Reduces network traffic
- Business logic in database

# Procedure with cursor
CREATE PROCEDURE ProcessOrders
AS
BEGIN
    DECLARE @OrderId INT;
    DECLARE @Total DECIMAL(10,2);
    
    DECLARE order_cursor CURSOR FOR
        SELECT Id FROM Orders WHERE Status = 'Pending';
    
    OPEN order_cursor;
    FETCH NEXT FROM order_cursor INTO @OrderId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process order
        UPDATE Orders
        SET Status = 'Processed'
        WHERE Id = @OrderId;
        
        FETCH NEXT FROM order_cursor INTO @OrderId;
    END;
    
    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END;

# Dynamic SQL
CREATE PROCEDURE SearchProducts
    @SearchTerm NVARCHAR(100)
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX) = '
        SELECT * FROM Products
        WHERE 1=1';
    
    IF @SearchTerm IS NOT NULL
        SET @Sql = @Sql + ' AND Name LIKE @SearchTerm';
    
    EXEC sp_executesql @Sql, N'@SearchTerm NVARCHAR(100)', @SearchTerm;
END;

💡 Stored Procedure Tips

• Better performance (precompiled)
• Security (encapsulation)
• Consistency (same logic)
• Reduces network traffic
• Business logic in database