Create a stored procedure to insert new data into a table
Creating a Stored Procedure for Data Insertion in SQL Server

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.
SET NOCOUNT ON;
at the beginning of your stored procedures. This prevents SQL Server from sending messages back to the client for each statement that affects rows, which can improve performance, especially in loops or when many statements are executed.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.
@parameters
) are inherently safe. If you must use dynamic SQL, always use sp_executesql
with proper parameterization.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.
5. Consider Transactions (Optional but Recommended)
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.