This post is part of a series dedicated to database access. This is the stored procedure that creates new rows in the database.

It is very simple; it has all the columns of the table to which it belongs as input parameters. If the primary key is an identity it is converted to an output parameter in order to send the new created key to the calling layer for future use.

In order to retrieve the value of the primary key I use the IDENT_CURRENT function.

Here is an example of how it works:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_INS]
--PK as OUTPUT
@pPK_Destruccio int OUTPUT,
@pFK_TAAD int, 
@pFK_Arxiu int, 
@pAny_obertura int, 
@pAny_tancament int, 
@pMetres decimal(5, 1), @pData_Destruccio datetime, 
@pObservacions varchar(2000), 
@pbCataleg nchar(20), 
@pSerie_documental varchar(500), 
@pMK_UsuariAlta varchar(50), 
@pMK_DataAlta datetime, @pMK_UsuariModif varchar(50), 
@pMK_DataModif datetime, @pFK_Estat int, 
@pNumExpedients int, 
@pFK_Classificacio int, 
@pFK_Especifica int AS
BEGIN
 INSERT INTO DI_EXP_DESTRUCCIO (
 FK_TAAD, 
 FK_Arxiu, 
 Any_obertura, 
 Any_tancament, 
 Metres, 
 Data_Destruccio, 
 Observacions, 
 bCataleg, 
 Serie_documental, 
 MK_UsuariAlta, 
 MK_DataAlta, 
 MK_UsuariModif, 
 MK_DataModif, 
 FK_Estat, 
 NumExpedients, 
 FK_Classificacio, 
 FK_Especifica
 ) VALUES (
--PK is not included
 @pFK_TAAD, 
 @pFK_Arxiu, 
 @pAny_obertura, 
 @pAny_tancament, 
 @pMetres, 
 @pData_Destruccio, 
 @pObservacions, 
 @pbCataleg, 
 @pSerie_documental, 
 @pMK_UsuariAlta, 
 @pMK_DataAlta, 
 @pMK_UsuariModif, 
 @pMK_DataModif, 
 @pFK_Estat, 
 @pNumExpedients, 
 @pFK_Classificacio, 
 @pFK_Especifica
 );
--PK retrieval
SET @pPK_Destruccio = IDENT_CURRENT('DI_EXP_DESTRUCCIO')
END