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