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

- 07.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;

🎯 Advanced Procedures

-- 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;

-- Cursor (processing rows)
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;

-- Return table (function)
CREATE FUNCTION GetUserOrders(@UserId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM Orders
    WHERE UserId = @UserId
);

-- Use function
SELECT * FROM GetUserOrders(1);

💡 Stored Procedure Benefits

  • 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 Queries Get Slower as Data Grows

SQL Queries Get Slower as Data Grows

SQL: DELETE vs TRUNCATE — Know the Difference

SQL: DELETE vs TRUNCATE — Know the Difference

SQL Server: Fix a Parameterized Query That Runs Fast Once and Slow Forever After Because of Paramete...

SQL Server: Fix a Parameterized Query That Runs Fast Once and Slow Forever After Because of Paramete...

Views: 12

Post navigation

.NET Core: Use SignalR for Real-Time Web Apps
C#: Use Pattern Matching for Elegant Code

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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