Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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:

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.






More SQL Server Solutions




Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

*Name    *Email    Notify for updates 

Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools