1-- Basic syntax
2CREATE PROCEDURE SchemaName.ProcedureName
3AS
4BEGIN
5    -- T-SQL statements
6END;
7GO
8
9-- Example: Get all active customers
10CREATE PROCEDURE Customer.usp_GetActiveCustomers
11AS
12BEGIN
13    SET NOCOUNT ON;  -- Không trả về row count message
14    
15    SELECT 
16        CustomerID,
17        FirstName,
18        LastName,
19        Email,
20        Phone
21    FROM Customer.Customer
22    WHERE IsActive = 1
23    ORDER BY LastName, FirstName;
24END;
25GO
26
27-- Execute
28EXEC Customer.usp_GetActiveCustomers;
29-- Hoặc
30EXECUTE Customer.usp_GetActiveCustomers;

1-- Parameters
2CREATE PROCEDURE Customer.usp_GetCustomerByID
3    @CustomerID INT  -- Input parameter
4AS
5BEGIN
6    SET NOCOUNT ON;
7    
8    SELECT 
9        CustomerID,
10        FirstName,
11        LastName,
12        Email,
13        Phone,
14        DateOfBirth,
15        CreatedDate
16    FROM Customer.Customer
17    WHERE CustomerID = @CustomerID;
18END;
19GO
20
21-- Execute với parameter
22EXEC Customer.usp_GetCustomerByID @CustomerID = 1;
23-- Hoặc positional
24EXEC Customer.usp_GetCustomerByID 1;

1CREATE PROCEDURE Sales.usp_GetOrders
2    @CustomerID INT = NULL,           -- Optional, default NULL
3    @Status VARCHAR(20) = NULL,       -- Optional
4    @FromDate DATE = NULL,            -- Optional
5    @ToDate DATE = NULL,              -- Optional
6    @TopN INT = 100                   -- Default value
7AS
8BEGIN
9    SET NOCOUNT ON;
10    
11    SELECT TOP (@TopN)
12        o.OrderID,
13        o.OrderNumber,
14        o.OrderDate,
15        o.Status,
16        o.TotalAmount,
17        c.FirstName + ' ' + c.LastName AS CustomerName
18    FROM Sales.[Order] o
19    JOIN Customer.Customer c ON o.CustomerID = c.CustomerID
20    WHERE 
21        (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
22        AND (@Status IS NULL OR o.Status = @Status)
23        AND (@FromDate IS NULL OR o.OrderDate >= @FromDate)
24        AND (@ToDate IS NULL OR o.OrderDate <= @ToDate)
25    ORDER BY o.OrderDate DESC;
26END;
27GO
28
29-- Execute với different params
30EXEC Sales.usp_GetOrders;                                      -- All orders (top 100)
31EXEC Sales.usp_GetOrders @CustomerID = 5;                      -- Customer 5's orders
32EXEC Sales.usp_GetOrders @Status = 'Pending';                  -- Pending orders
33EXEC Sales.usp_GetOrders @FromDate = '2024-01-01', @TopN = 50; -- 2024 orders, top 50

1CREATE PROCEDURE Customer.usp_CreateCustomer
2    @FirstName NVARCHAR(50),
3    @LastName NVARCHAR(50),
4    @Email VARCHAR(100),
5    @Phone VARCHAR(20) = NULL,
6    @NewCustomerID INT OUTPUT,        -- Output parameter
7    @ErrorMessage NVARCHAR(500) OUTPUT
8AS
9BEGIN
10    SET NOCOUNT ON;
11    
12    -- Validate email
13    IF EXISTS (SELECT 1 FROM Customer.Customer WHERE Email = @Email)
14    BEGIN
15        SET @NewCustomerID = 0;
16        SET @ErrorMessage = N'Email already exists';
17        RETURN;
18    END
19    
20    -- Insert customer
21    INSERT INTO Customer.Customer (FirstName, LastName, Email, Phone)
22    VALUES (@FirstName, @LastName, @Email, @Phone);
23    
24    -- Get new ID
25    SET @NewCustomerID = SCOPE_IDENTITY();
26    SET @ErrorMessage = NULL;
27END;
28GO
29
30-- Execute with OUTPUT
31DECLARE @NewID INT, @Error NVARCHAR(500);
32
33EXEC Customer.usp_CreateCustomer
34    @FirstName = N'Nguyễn',
35    @LastName = N'Văn A',
36    @Email = 'nguyenvana@email.com',
37    @Phone = '0901234567',
38    @NewCustomerID = @NewID OUTPUT,
39    @ErrorMessage = @Error OUTPUT;
40
41SELECT @NewID AS NewCustomerID, @Error AS ErrorMessage;

1-- Return value (integer) thường dùng cho status code
2CREATE PROCEDURE Sales.usp_CancelOrder
3    @OrderID INT
4AS
5BEGIN
6    SET NOCOUNT ON;
7    
8    -- Check if order exists
9    IF NOT EXISTS (SELECT 1 FROM Sales.[Order] WHERE OrderID = @OrderID)
10        RETURN -1;  -- Not found
11    
12    -- Check if already cancelled
13    IF EXISTS (SELECT 1 FROM Sales.[Order] WHERE OrderID = @OrderID AND Status = 'Cancelled')
14        RETURN -2;  -- Already cancelled
15    
16    -- Check if delivered (cannot cancel)
17    IF EXISTS (SELECT 1 FROM Sales.[Order] WHERE OrderID = @OrderID AND Status = 'Delivered')
18        RETURN -3;  -- Cannot cancel delivered order
19    
20    -- Cancel order
21    UPDATE Sales.[Order]
22    SET Status = 'Cancelled',
23        ModifiedDate = SYSDATETIME()
24    WHERE OrderID = @OrderID;
25    
26    RETURN 0;  -- Success
27END;
28GO
29
30-- Execute and check return value
31DECLARE @Result INT;
32EXEC @Result = Sales.usp_CancelOrder @OrderID = 123;
33
34SELECT CASE @Result
35    WHEN 0 THEN 'Order cancelled successfully'
36    WHEN -1 THEN 'Order not found'
37    WHEN -2 THEN 'Order already cancelled'
38    WHEN -3 THEN 'Cannot cancel delivered order'
39    ELSE 'Unknown error'
40END AS ResultMessage;

1CREATE PROCEDURE Sales.usp_ProcessOrder
2    @OrderID INT
3AS
4BEGIN
5    SET NOCOUNT ON;
6    
7    BEGIN TRY
8        BEGIN TRANSACTION;
9        
10        -- Business logic here
11        UPDATE Sales.[Order]
12        SET Status = 'Processing'
13        WHERE OrderID = @OrderID;
14        
15        -- Deduct inventory
16        UPDATE p
17        SET p.StockQuantity = p.StockQuantity - od.Quantity
18        FROM Inventory.Product p
19        JOIN Sales.OrderDetail od ON p.ProductID = od.ProductID
20        WHERE od.OrderID = @OrderID;
21        
22        COMMIT TRANSACTION;
23    END TRY
24    BEGIN CATCH
25        -- Rollback on error
26        IF @@TRANCOUNT > 0
27            ROLLBACK TRANSACTION;
28        
29        -- Re-throw error
30        THROW;
31    END CATCH
32END;
33GO

1CREATE PROCEDURE Sales.usp_CreateOrder
2    @CustomerID INT,
3    @Products dbo.OrderItemType READONLY,  -- Table-valued parameter
4    @NewOrderID INT OUTPUT
5AS
6BEGIN
7    SET NOCOUNT ON;
8    
9    DECLARE @ErrorNumber INT,
10            @ErrorMessage NVARCHAR(4000),
11            @ErrorSeverity INT,
12            @ErrorState INT,
13            @ErrorProcedure NVARCHAR(200),
14            @ErrorLine INT;
15    
16    BEGIN TRY
17        BEGIN TRANSACTION;
18        
19        -- Validate customer
20        IF NOT EXISTS (SELECT 1 FROM Customer.Customer WHERE CustomerID = @CustomerID AND IsActive = 1)
21        BEGIN
22            RAISERROR(N'Customer not found or inactive', 16, 1);
23        END
24        
25        -- Generate order number
26        DECLARE @OrderNumber VARCHAR(20) = 'ORD-' + FORMAT(SYSDATETIME(), 'yyyyMMddHHmmss');
27        
28        -- Create order header
29        INSERT INTO Sales.[Order] (OrderNumber, CustomerID, OrderDate, Status)
30        VALUES (@OrderNumber, @CustomerID, SYSDATETIME(), 'Pending');
31        
32        SET @NewOrderID = SCOPE_IDENTITY();
33        
34        -- Create order details
35        INSERT INTO Sales.OrderDetail (OrderID, ProductID, Quantity, UnitPrice)
36        SELECT 
37            @NewOrderID,
38            p.ProductID,
39            p.Quantity,
40            pr.UnitPrice
41        FROM @Products p
42        JOIN Inventory.Product pr ON p.ProductID = pr.ProductID;
43        
44        -- Update order totals
45        UPDATE Sales.[Order]
46        SET SubTotal = (
47            SELECT SUM(LineTotal) 
48            FROM Sales.OrderDetail 
49            WHERE OrderID = @NewOrderID
50        )
51        WHERE OrderID = @NewOrderID;
52        
53        COMMIT TRANSACTION;
54        
55    END TRY
56    BEGIN CATCH
57        IF @@TRANCOUNT > 0
58            ROLLBACK TRANSACTION;
59        
60        -- Capture error details
61        SET @ErrorNumber = ERROR_NUMBER();
62        SET @ErrorMessage = ERROR_MESSAGE();
63        SET @ErrorSeverity = ERROR_SEVERITY();
64        SET @ErrorState = ERROR_STATE();
65        SET @ErrorProcedure = ERROR_PROCEDURE();
66        SET @ErrorLine = ERROR_LINE();
67        
68        -- Log error
69        INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine, ErrorTime)
70        VALUES (@ErrorNumber, @ErrorMessage, @ErrorProcedure, @ErrorLine, SYSDATETIME());
71        
72        -- Re-raise error
73        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
74        
75        SET @NewOrderID = 0;
76    END CATCH
77END;
78GO

1-- 1. Create Type
2CREATE TYPE dbo.OrderItemType AS TABLE (
3    ProductID INT NOT NULL,
4    Quantity INT NOT NULL
5);
6GO
7
8-- 2. Use in procedure
9CREATE PROCEDURE Sales.usp_BulkInsertOrderDetails
10    @OrderID INT,
11    @Items dbo.OrderItemType READONLY
12AS
13BEGIN
14    SET NOCOUNT ON;
15    
16    INSERT INTO Sales.OrderDetail (OrderID, ProductID, Quantity, UnitPrice)
17    SELECT 
18        @OrderID,
19        i.ProductID,
20        i.Quantity,
21        p.UnitPrice
22    FROM @Items i
23    JOIN Inventory.Product p ON i.ProductID = p.ProductID;
24END;
25GO
26
27-- 3. Call procedure
28DECLARE @OrderItems dbo.OrderItemType;
29INSERT INTO @OrderItems (ProductID, Quantity) VALUES (1, 2), (2, 1), (3, 5);
30
31EXEC Sales.usp_BulkInsertOrderDetails @OrderID = 100, @Items = @OrderItems;

1CREATE PROCEDURE dbo.usp_DynamicSearch
2    @TableName NVARCHAR(128),
3    @SearchColumn NVARCHAR(128),
4    @SearchValue NVARCHAR(100)
5AS
6BEGIN
7    SET NOCOUNT ON;
8    
9    DECLARE @SQL NVARCHAR(MAX);
10    
11    -- Validate table exists
12    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TableName)
13    BEGIN
14        RAISERROR('Table not found', 16, 1);
15        RETURN;
16    END
17    
18    -- Build dynamic SQL (use QUOTENAME to prevent injection)
19    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + 
20               N' WHERE ' + QUOTENAME(@SearchColumn) + N' LIKE @Value';
21    
22    -- Execute with parameterized value
23    EXEC sp_executesql @SQL, 
24        N'@Value NVARCHAR(100)', 
25        @Value = '%' + @SearchValue + '%';
26END;
27GO
28
29-- ⚠️ WARNING: Dynamic SQL có risk. Luôn dùng QUOTENAME và sp_executesql với parameters

1-- Cursor example (thường nên tránh, dùng set-based operations)
2CREATE PROCEDURE Sales.usp_UpdateOrderStatuses
3AS
4BEGIN
5    SET NOCOUNT ON;
6    
7    DECLARE @OrderID INT, @DaysOld INT;
8    
9    -- Cursor for orders
10    DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
11        SELECT OrderID, DATEDIFF(DAY, OrderDate, GETDATE()) AS DaysOld
12        FROM Sales.[Order]
13        WHERE Status = 'Shipped';
14    
15    OPEN order_cursor;
16    
17    FETCH NEXT FROM order_cursor INTO @OrderID, @DaysOld;
18    
19    WHILE @@FETCH_STATUS = 0
20    BEGIN
21        -- Auto-complete if shipped > 7 days
22        IF @DaysOld > 7
23        BEGIN
24            UPDATE Sales.[Order]
25            SET Status = 'Delivered'
26            WHERE OrderID = @OrderID;
27        END
28        
29        FETCH NEXT FROM order_cursor INTO @OrderID, @DaysOld;
30    END
31    
32    CLOSE order_cursor;
33    DEALLOCATE order_cursor;
34END;
35GO
36
37-- ✅ Better: Set-based approach
38UPDATE Sales.[Order]
39SET Status = 'Delivered'
40WHERE Status = 'Shipped'
41AND DATEDIFF(DAY, OrderDate, GETDATE()) > 7;

1-- ALTER to modify
2ALTER PROCEDURE Customer.usp_GetActiveCustomers
3AS
4BEGIN
5    SET NOCOUNT ON;
6    
7    SELECT 
8        CustomerID,
9        FirstName,
10        LastName,
11        Email,
12        Phone,
13        CreatedDate  -- Added column
14    FROM Customer.Customer
15    WHERE IsActive = 1
16    ORDER BY CreatedDate DESC;  -- Changed order
17END;
18GO
19
20-- CREATE OR ALTER (SQL Server 2016+)
21CREATE OR ALTER PROCEDURE Customer.usp_GetActiveCustomers
22AS
23BEGIN
24    -- ...
25END;
26GO
27
28-- DROP
29DROP PROCEDURE Customer.usp_GetActiveCustomers;
30DROP PROCEDURE IF EXISTS Customer.usp_GetActiveCustomers;

1-- Xem list SPs
2SELECT 
3    SCHEMA_NAME(schema_id) AS SchemaName,
4    name AS ProcedureName,
5    create_date,
6    modify_date
7FROM sys.procedures
8ORDER BY SchemaName, name;
9
10-- Xem definition
11EXEC sp_helptext 'dbo.uspGetEmployeeManagers';
12
13-- Execute AdventureWorks SP
14EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 5;
15
16-- Study pattern: AdventureWorks SPs
17EXEC dbo.uspGetManagerEmployees @BusinessEntityID = 2;
18EXEC dbo.uspSearchCandidateResumes @searchString = 'SQL';

1-- Prefix với usp_ (user stored procedure)
2usp_GetCustomers
3usp_CreateOrder
4usp_UpdateProduct
5
6-- Verb + Noun pattern
7usp_Get...      -- Retrieve data
8usp_Create...   -- Insert new
9usp_Update...   -- Modify existing
10usp_Delete...   -- Remove
11usp_Search...   -- Search/filter
12usp_Process...  -- Complex operations
13
14-- Schema organization
15Customer.usp_GetByID
16Sales.usp_CreateOrder
17Inventory.usp_UpdateStock

1CREATE OR ALTER PROCEDURE SchemaName.usp_ProcedureName
2    -- Parameters
3    @Param1 INT,
4    @Param2 NVARCHAR(100) = NULL,
5    @OutputParam INT OUTPUT
6AS
7BEGIN
8    /*
9    =============================================
10    Author:         Your Name
11    Create date:    2024-01-15
12    Description:    Brief description of what SP does
13    
14    Modification History:
15    Date        Author      Description
16    ----------  ----------  ---------------------
17    2024-01-15  Your Name   Initial creation
18    =============================================
19    */
20    
21    SET NOCOUNT ON;
22    SET XACT_ABORT ON;  -- Auto rollback on error
23    
24    -- Validate inputs
25    IF @Param1 IS NULL
26    BEGIN
27        RAISERROR('Param1 is required', 16, 1);
28        RETURN -1;
29    END
30    
31    BEGIN TRY
32        BEGIN TRANSACTION;
33        
34        -- Business logic here
35        
36        COMMIT TRANSACTION;
37        RETURN 0;  -- Success
38        
39    END TRY
40    BEGIN CATCH
41        IF @@TRANCOUNT > 0
42            ROLLBACK TRANSACTION;
43        
44        -- Log error or handle
45        THROW;
46    END CATCH
47END;
48GO

1-- ✅ DO
2SET NOCOUNT ON;                    -- Reduce network traffic
3SET XACT_ABORT ON;                 -- Consistent error handling
4Use parameters, not string concat   -- Prevent injection, enable plan cache
5
6-- ❌ DON'T
7SELECT *                           -- Select specific columns
8EXEC('SELECT...' + @var)          -- Avoid dynamic SQL when possible
9Nested cursors                     -- Use set-based operations

1-- Complete CRUD for Product
2
3-- 1. READ - Get all products
4CREATE OR ALTER PROCEDURE Inventory.usp_GetProducts
5    @CategoryID INT = NULL,
6    @IsActive BIT = NULL,
7    @SearchTerm NVARCHAR(100) = NULL
8AS
9BEGIN
10    SET NOCOUNT ON;
11    
12    SELECT 
13        p.ProductID,
14        p.SKU,
15        p.ProductName,
16        p.Description,
17        c.CategoryName,
18        p.UnitPrice,
19        p.StockQuantity,
20        p.IsActive
21    FROM Inventory.Product p
22    JOIN Inventory.Category c ON p.CategoryID = c.CategoryID
23    WHERE 
24        (@CategoryID IS NULL OR p.CategoryID = @CategoryID)
25        AND (@IsActive IS NULL OR p.IsActive = @IsActive)
26        AND (@SearchTerm IS NULL 
27             OR p.ProductName LIKE '%' + @SearchTerm + '%'
28             OR p.SKU LIKE '%' + @SearchTerm + '%')
29    ORDER BY p.ProductName;
30END;
31GO
32
33-- 2. READ - Get single product
34CREATE OR ALTER PROCEDURE Inventory.usp_GetProductByID
35    @ProductID INT
36AS
37BEGIN
38    SET NOCOUNT ON;
39    
40    SELECT 
41        p.*,
42        c.CategoryName
43    FROM Inventory.Product p
44    JOIN Inventory.Category c ON p.CategoryID = c.CategoryID
45    WHERE p.ProductID = @ProductID;
46END;
47GO
48
49-- 3. CREATE
50CREATE OR ALTER PROCEDURE Inventory.usp_CreateProduct
51    @SKU VARCHAR(20),
52    @ProductName NVARCHAR(200),
53    @Description NVARCHAR(MAX) = NULL,
54    @CategoryID INT,
55    @UnitPrice DECIMAL(19,4),
56    @CostPrice DECIMAL(19,4) = NULL,
57    @StockQuantity INT = 0,
58    @NewProductID INT OUTPUT
59AS
60BEGIN
61    SET NOCOUNT ON;
62    
63    -- Validate SKU unique
64    IF EXISTS (SELECT 1 FROM Inventory.Product WHERE SKU = @SKU)
65    BEGIN
66        RAISERROR('SKU already exists', 16, 1);
67        RETURN -1;
68    END
69    
70    -- Validate category exists
71    IF NOT EXISTS (SELECT 1 FROM Inventory.Category WHERE CategoryID = @CategoryID)
72    BEGIN
73        RAISERROR('Category not found', 16, 1);
74        RETURN -2;
75    END
76    
77    INSERT INTO Inventory.Product (SKU, ProductName, Description, CategoryID, UnitPrice, CostPrice, StockQuantity)
78    VALUES (@SKU, @ProductName, @Description, @CategoryID, @UnitPrice, @CostPrice, @StockQuantity);
79    
80    SET @NewProductID = SCOPE_IDENTITY();
81    RETURN 0;
82END;
83GO
84
85-- 4. UPDATE
86CREATE OR ALTER PROCEDURE Inventory.usp_UpdateProduct
87    @ProductID INT,
88    @ProductName NVARCHAR(200),
89    @Description NVARCHAR(MAX) = NULL,
90    @CategoryID INT,
91    @UnitPrice DECIMAL(19,4),
92    @CostPrice DECIMAL(19,4) = NULL,
93    @IsActive BIT = 1
94AS
95BEGIN
96    SET NOCOUNT ON;
97    
98    -- Check exists
99    IF NOT EXISTS (SELECT 1 FROM Inventory.Product WHERE ProductID = @ProductID)
100    BEGIN
101        RAISERROR('Product not found', 16, 1);
102        RETURN -1;
103    END
104    
105    UPDATE Inventory.Product
106    SET ProductName = @ProductName,
107        Description = @Description,
108        CategoryID = @CategoryID,
109        UnitPrice = @UnitPrice,
110        CostPrice = @CostPrice,
111        IsActive = @IsActive,
112        ModifiedDate = SYSDATETIME()
113    WHERE ProductID = @ProductID;
114    
115    RETURN 0;
116END;
117GO
118
119-- 5. DELETE (soft delete)
120CREATE OR ALTER PROCEDURE Inventory.usp_DeleteProduct
121    @ProductID INT
122AS
123BEGIN
124    SET NOCOUNT ON;
125    
126    -- Check exists
127    IF NOT EXISTS (SELECT 1 FROM Inventory.Product WHERE ProductID = @ProductID)
128    BEGIN
129        RAISERROR('Product not found', 16, 1);
130        RETURN -1;
131    END
132    
133    -- Soft delete
134    UPDATE Inventory.Product
135    SET IsActive = 0,
136        ModifiedDate = SYSDATETIME()
137    WHERE ProductID = @ProductID;
138    
139    RETURN 0;
140END;
141GO