SQL Server Stored Procedure - INSERT - Example

Source code to create and add "sql insert stored procedure" to catalog

The following example is for creating a simple insert stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like SQL Server Management Studio or dbOrchestra.

IF ( OBJECT_ID('dbo.sp_Students_INS_byPK') IS NOT NULL ) 
   DROP PROCEDURE dbo.sp_Students_INS_byPK
GO

CREATE PROCEDURE dbo.sp_Students_INS_byPK
       @student_id                     INT                      , 
       @password                       VARCHAR(15)      = NULL  , 
       @active_flg                     TINYINT                  , 
       @lastname                       VARCHAR(30)      = NULL  , 
       @birth_dttm                     DATETIME         = NULL  , 
       @gpa                            INT              = NULL  , 
       @is_on_staff                    TINYINT                   
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.Students
          ( 
            student_id                   ,
            password                     ,
            active_flg                   ,
            lastname                     ,
            birth_dttm                   ,
            gpa                          ,
            is_on_staff                  
          ) 
     VALUES 
          ( 
            @student_id                   ,
            @password                     ,
            @active_flg                   ,
            @lastname                     ,
            @birth_dttm                   ,
            @gpa                          ,
            @is_on_staff                  
          ) 

END 

GO

You will note that just like in a INSERT statement you do not have to use all of the columns available when creating a stored procedure. You must however populate all columnar data associated with the PK (primary key), and columns associated with unique indexes (note: there are exceptions to this, but they will not addressed here), and columns defined in the ddl as "NOT NULL".

Executing the sql insert stored procedure

Execute insert stored procedure

To run the insert stored procedure you need to supply a value to the student_id variable as well as populate all required data. In this example I have included a cross section of columns for your reference, as well as the datatype associated with the columns in our SQL INSERT.


-- @student_id   IN  INT
-- @password     IN  VARCHAR(15)
-- @active_flg   IN  TINYINT
-- @lastname     IN  VARCHAR(30)
-- @birth_dttm   IN  DATETIME
-- @gpa          IN  INT
-- @is_on_staff  IN  TINYINT


     @student_id   =    25   , 
     @password     =    'foobar'   , 
     @active_flg   =    1   , 
     @lastname     =    'Frapples'   , 
     @birth_dttm   =    '1962-11-14'   , 
     @gpa          =    4  , 
     @is_on_staff  =    1  

GO

A few thing to point out regarding the code above. Datetime and character data is entered using single quoted string. Integer data is entered without quotes. I also included all columns that were defined as NOT NULL. I also included the password column which is part of a unique index. You will note that I did not include columns for some of the non-unique indexes (soc_sec_num, other_id_num, and driver_license_num). I did this intentionally to demonstrate that they are not required columns. Having said this, in the "real world" one would only put indexes on columns where the intent was to actually collect the data for that column. I just wanted to make a technical point here. The pragmatic point is that you would want to expose columns that are part of indexes.

What stored row looks like after the SQL INSERT

I want to call you attention to the fact that all columns that are not referenced in the query get set to null. Also, if the schema had default values defined these would also get stored for that column when the rows gets inserted.

   student_id                   = 25
   password                     = 'foobar'
   employee_id_fk               = null
   customer_id_fk               = null
   active_flg                   = 1
   lastname                     = 'Frapples'
   firstname                    = null
   middlename                   = null
   prefix_name                  = null
   suffix_name                  = null
   gender_code                  = null
   birth_dttm                   = '1962-11-14 00:00:00.0'
   birth_city_nm                = null
   birth_state_code             = null
   birth_country_code           = null
   gpa                          = 4
   is_on_staff                  = 1
   soc_sec_num                  = null
   driver_license_num           = null
   driver_license_state_code    = null
   driver_license_country_ocde  = null
   other_id_num                 = null
   citizen_country_code         = null
   marital_status_code          = null
   race_code                    = null
   ethnic_group_code            = null
   veteran_military_status_code = null
   fin_alert_code               = null
   inactive_date                = null
   inactive_reason              = null
   employee_inactivating        = null

Link to schema for Students table

If you find this site useful and are a book buyer/reader... You can support my site by clicking on one of the Links below to Abe books By doing this, I will receive a small commision on your purchase and making your purchase will not cost you any more money than if you went directly the site yourself! Abe Books sells books at a fraction of the original cost. I have purchased many books from them originally cost $40.00 for as little as $5 to $10 dollars including shipping costs Click on applicable icon to find books at Abe Books on ...
SQL Server 2008	SQL Server 2005	SQL Server 2000	T-SQL

Abe Books is one of my affiliates. I only use affiliates that I also purchase products from. I love Abe books. If it is printed they likely have a copy. I have also included a generic link to Abe Books below. Once on their site, you will find that they have a nice, easy to use search engine so to find what you are looking for.