Create a stored procedure to insert new data into a table

Learn create a stored procedure to insert new data into a table with practical examples, diagrams, and best practices. Covers sql, sql-server, stored-procedures development techniques with visual e...

Creating a Stored Procedure for Data Insertion in SQL Server

Hero image for Create a stored procedure to insert new data into a table

Learn how to design and implement a robust SQL Server stored procedure to safely and efficiently insert new records into your database tables, including best practices for parameter handling and error management.

Stored procedures are powerful tools in SQL Server that encapsulate one or more SQL statements into a logical unit. They offer numerous benefits, including improved performance, enhanced security, reduced network traffic, and better maintainability. This article will guide you through the process of creating a stored procedure specifically designed for inserting new data into a table, focusing on best practices and common considerations.

Understanding the Basics of Stored Procedures

Before diving into the creation process, it's essential to understand the fundamental components of a stored procedure. A stored procedure typically includes a CREATE PROCEDURE statement, a name for the procedure, a list of parameters (input and output), and the SQL statements that define its logic. For data insertion, input parameters are crucial as they allow you to pass the values for the new record.

flowchart TD
    A[Start] --> B{Define Procedure Name and Parameters}
    B --> C{Construct INSERT Statement}
    C --> D{Add Error Handling (TRY...CATCH)}
    D --> E{Execute INSERT}
    E --> F{Check @@ROWCOUNT}
    F{@@ROWCOUNT > 0?} -- Yes --> G[Commit Transaction (if applicable)]
    F{@@ROWCOUNT > 0?} -- No --> H[Rollback Transaction (if applicable)]
    G --> I[Return Success]
    H --> J[Return Failure/Error]
    I --> K[End]
    J --> K[End]

Flowchart of a typical stored procedure for data insertion

Designing Your Insertion Stored Procedure

When designing an insertion stored procedure, consider the table structure, data types, and any constraints (e.g., NOT NULL, UNIQUE). Each column that requires a value for insertion should ideally have a corresponding input parameter in your stored procedure. It's also good practice to include error handling to manage potential issues during the insertion process, such as duplicate keys or data type mismatches.

CREATE PROCEDURE dbo.InsertNewProduct
    @ProductName NVARCHAR(255),
    @ProductDescription NVARCHAR(MAX) = NULL,
    @Price DECIMAL(10, 2),
    @StockQuantity INT,
    @CategoryID INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents the count of the number of rows affected from being returned

    BEGIN TRY
        INSERT INTO Products (ProductName, ProductDescription, Price, StockQuantity, CategoryID, CreatedDate)
        VALUES (@ProductName, @ProductDescription, @Price, @StockQuantity, @CategoryID, GETDATE());

        -- Optionally return the ID of the newly inserted row
        SELECT SCOPE_IDENTITY() AS NewProductID;
    END TRY
    BEGIN CATCH
        -- Log error details or re-raise the error
        DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        -- Or simply print the error:
        -- PRINT 'Error inserting product: ' + @ErrorMessage;
    END CATCH
END;

Example SQL Server stored procedure for inserting a new product into a 'Products' table.

Executing and Testing the Stored Procedure

Once your stored procedure is created, you can execute it using the EXEC or EXECUTE command, passing the required parameters. It's crucial to test your procedure with various scenarios, including valid data, missing required data, incorrect data types, and data that might violate unique constraints, to ensure it behaves as expected and handles errors gracefully.

-- Example 1: Successful insertion
EXEC dbo.InsertNewProduct
    @ProductName = 'Laptop Pro X',
    @ProductDescription = 'High-performance laptop for professionals.',
    @Price = 1200.00,
    @StockQuantity = 50,
    @CategoryID = 1;

-- Example 2: Insertion with optional parameter (NULL for description)
EXEC dbo.InsertNewProduct
    @ProductName = 'Wireless Mouse',
    @Price = 25.99,
    @StockQuantity = 200,
    @CategoryID = 2;

-- Example 3: Testing error handling (e.g., if CategoryID does not exist or Price is invalid)
-- This might raise an error depending on your table constraints
EXEC dbo.InsertNewProduct
    @ProductName = 'Invalid Product',
    @Price = -10.00, -- Assuming Price cannot be negative
    @StockQuantity = 10,
    @CategoryID = 999; -- Assuming CategoryID 999 does not exist

Examples of executing the dbo.InsertNewProduct stored procedure.

Advanced Considerations: Transactions and Output Parameters

For operations involving multiple steps that must succeed or fail as a single unit, wrap your INSERT statement within a TRANSACTION. This ensures data integrity. You can also use OUTPUT parameters to return values from the stored procedure, such as the NewProductID generated by SCOPE_IDENTITY(), which is useful for client applications.

CREATE PROCEDURE dbo.InsertNewProductWithTransaction
    @ProductName NVARCHAR(255),
    @ProductDescription NVARCHAR(MAX) = NULL,
    @Price DECIMAL(10, 2),
    @StockQuantity INT,
    @CategoryID INT,
    @NewProductID INT OUTPUT -- Output parameter to return the new ID
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        INSERT INTO Products (ProductName, ProductDescription, Price, StockQuantity, CategoryID, CreatedDate)
        VALUES (@ProductName, @ProductDescription, @Price, @StockQuantity, @CategoryID, GETDATE());

        SET @NewProductID = SCOPE_IDENTITY(); -- Get the ID of the newly inserted row

        COMMIT TRANSACTION; -- Commit if all successful
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION; -- Rollback on error

        DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;

Stored procedure demonstrating transaction management and an output parameter.

1. Define Table Schema

Ensure you have a clear understanding of the target table's columns, data types, and constraints. This will dictate the parameters needed for your stored procedure.

2. Create Stored Procedure Structure

Use CREATE PROCEDURE followed by a meaningful name. Define input parameters for each column you intend to populate, specifying their data types.

3. Implement INSERT Statement

Write the INSERT INTO statement, mapping the stored procedure's parameters to the table columns. Include GETDATE() for CreatedDate columns if applicable.

4. Add Error Handling

Wrap your INSERT statement in a BEGIN TRY...END TRY block and provide a BEGIN CATCH...END CATCH block to handle potential errors gracefully, logging or re-raising them.

For critical operations, use BEGIN TRANSACTION and COMMIT TRANSACTION with a ROLLBACK TRANSACTION in the CATCH block to maintain data integrity.

6. Test Thoroughly

Execute the stored procedure with various valid and invalid inputs to confirm it behaves as expected and handles errors correctly.