Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Stored Procedures for Database Logic

- 18.07.26 - ErcanOPAK

📦 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

“Stored procedures add logic to database. Performance, security, consistency. Essential for database development.”

— Database Developer

Related posts:

SQL Server: Fix a Column Default of GETDATE() Returning the Identical Timestamp for Every Row of a B...

SQL Server: Fix a Column Default of GETDATE() Returning the Identical Timestamp for Every Row of a B...

SQL: Use Indexes to Boost Query Performance

SQL: Use Indexes to Boost Query Performance

SQL Server: Find the Missing Index Silently Making Your Query 100x Slower — In One Query

SQL Server: Find the Missing Index Silently Making Your Query 100x Slower — In One Query

Views: 14

Post navigation

.NET Core: Add Globalization for Localization
C#: Use Null-Coalescing Operator (??) for Defaults

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com