INSERT stored procedure in SQL Server tutorial and example

solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL safe backup

SQL compliance manager

SQL diagnostic manager

SQL Sentry Performance Advisor

SQL Source Control

Tutorial Menu

Introduction Sample table Simple INSERT Example Verify Inserted Data INSERT with variables INSERT Stored Procedure Auto Generate INSERT INSERT with IDENTITY INSERT SELECT SELECT INTO

INSERT stored procedure in SQL Server

Overview

Generally speaking, in an application variables are used with stored procedures to insert records into a table. Since we have covered how to create and assign variables, let's see how to incorporate this into a stored procedure.

Explanation

Let's explain this code before we begin executing it:

The top two lines with the 'CREATE PROCEDURE' logic indicate a new stored procedure will be created followed by the input parameters and their associated data type. As you can see from the last example, the declarations section has been moved to the 'CREATE PROCEDURE' logic and the variables are not initialized at this point in time.
The 'AS' indicates the body of the stored procedure code will follow.
The next section starting with '/*' and ending with '*/' are comments. This stored procedure has a comment block that I typically use. For more information about comments, check out the Comment SQL Server TSQL Code tip.
The SET NOCOUNT ON logic indicates to SQL Server not to return the number of rows affected. For more information about SET NOCOUNT ON check out Reducing amount of network data for SQL Server stored procedures and the SET NOCOUNT ON Improves SQL Server Stored Procedure Performance tip.
The next set of logic performs the INSERT command and uses the variables from the 'CREATE PROCEDURE' logic.
The GO command is the batch terminator and ends the logic for this example.

CREATE PROCEDURE dbo.spINSERT_dbo_Customer @FirstName varchar(25), @LastName varchar(25), 
@PhoneNumber varchar(15), @EmailAddress varchar(25), @Priority int, @CreateDate datetime

AS

/*
----------------------------------------------------------------------------
-- Object Name: dbo.spINSERT_dbo_Customer
-- Project: Sample code
-- Business Process: Sample code
-- Purpose: Insert a record into a table
-- Detailed Description: Insert a record into the dbo.Customer table
-- Database: Test
-- Dependent Objects: None
-- Called By: Ad-hoc
-- Upstream Systems: N\A
-- Downstream Systems: N\A
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer  | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 09.15.2011 | JKadlec | Original code
--
*/

SET NOCOUNT ON

-- 1 - Declare variables

-- 2 - Initialize variables

-- 3 - Execute INSERT command
INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[PhoneNumber]
           ,[EmailAddress]
           ,[Priority]
           ,[CreateDate])
     VALUES
           (@FirstName
           ,@LastName
           ,@PhoneNumber
           ,@EmailAddress
           ,@Priority
           ,@CreateDate)
GO

With the code explanation out of the way, let's execute the stored procedure:

EXECUTE [dbo].[spINSERT_dbo_Customer] 
   @FirstName = 'Tommy'
  ,@LastName = 'Crabber'
  ,@PhoneNumber = '333-333-3333'
  ,@EmailAddress = 'tommy@KingCrabber.com'
  ,@Priority = 1
  ,@CreateDate = '2011-09-15'
GO

For more information about stored procedures, check out the SQL Server Stored Procedure Tutorial.

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement.

NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now

Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check.

Unlock the power of the Transaction log to discover unauthorized changes and recover lost data

Free Webinar - Backup compression and storage deduplication: A perfect match?