How to write a Store procedure in SQL server database (Microsoft) with output parameter and transaction

This is the article which explains how to write a Store procedure in SQL server database with output parameter to extract the recorded id which is just inserted.

Background: A table is created with name “CD” and parameter as  

CDID: Primary Key with auto increment active on this parameter
CD_Number : It stores the CD number marked on CD as achieves
CD_Name: It stores the name of CD
CD_Descp: It stores the Details about CD’s

Create the store procedure as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE CD_Insert_CDdata
(
     @CD_Number nvarchar(50)= null       -- if no values are passed, then this parameters are  set to null
    ,@CD_Name nvarchar(300)= null

    ,@CD_Descp nvarchar(max)= null
    ,@mode nvarchar (5)= '1'
    ,@CDID nvarchar(50)=null OUTPUT       -- imp note : output keyword is needed to make the parameter act like output (it can act as output as well as input too)

)

 AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        IF (@mode ='1')
            BEGIN TRY
               BEGIN TRANSACTION    -- Start the transaction

                    -- insert Data In Cd table
                       INSERT INTO CD (CD_Number,CD_Name, CD_Descp)
                                 VALUES (@CD_Number,@CD_Name,@CD_Descp)                  
                       
                   SET @CDID = Scope_Identity()

                    --SET @CDID = (Select Scope_Identity() as CDID)   -- (one more way)

               select 'success'  -- to send success as return value
               -- If we reach here, success!
               COMMIT

            END TRY
            BEGIN CATCH

              -- OMG, there was an Error/Exception….
              IF @@TRANSCOUNT > 0
                 ROLLBACK  --rollback the insert query above

              -- Raise an Exception /error with the details of the exception
              DECLARE @ErrorMsg nvarchar(4000), @ErrorSeverity int

              SELECT @ErrorMsg = ERROR_MESSAGE(),
                     @ErrorSeverity = ERROR_SEVERITY()

              RAISERROR(@ErrorMsg, @ErrorSeverity, 1)

                SET @CDID = @ErrorMsg + ' failed'

                    select 'failure'

            END CATCH   

        End

Note:

-          1. Here @CDID is acting like a output parameter. To make it as output parameter, keyword “output” is important to mention while declaring variables or otherwise it is accepted as input parameter by default.

 

-          2.In Sqlserver 2005 and  sql server 2008, if parameter is declared as output with keyword as “Output”, then that parameter acts like both input as well as output parameter. This means you can send the values to Procedure via parameters or also set the values to parameter in Store procedure.

-          3. But there is a small change in declaring a parameter in ADO.NET as output / In/out parameter
System.Data.SqlClient.SqlParameter CDID_param = mSqlcmd.Parameters.Add("@CDID", SqlDbType.NVarChar, 50);   // this parameter is  used to get the record number inserted
            // a.    CDID_param.Direction = ParameterDirection.Output; 
             //b.    CDID_param.Direction = ParameterDirection.InputOutput;
a > tells you that CDID_param ( as "@CDID” ) is only a Output parameter and cannot be used to pass the value to store procedure
b>> tells you that CDID_param ( as "@CDID” ) is Output as well as input parameter, you can either pass or retrieve value via parameters.

-          4. Use “Scope_Identity()”  because it returns the ID of latest inserted records in given table in store procedure   in same scope (same connection, same user, same table ) and do not gives any other ID…. While in case of @@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table.(largely affected by trigger , @@identity returns trigger affected table’s ID).

-          5. I used a Transaction always for DDL statements like insert update delete , so if anything goes wrong in middle , it revoke the inserts by rollback statement and the database is clean without incomplete /incorrect data.

-          6. Transaction is very essential and important in case of multiple table / row  insert  or update. If there are more than 1 statements to be executed one after another and if in middle any statements throws a Exception / Error in whole transaction, then all the updates/ inserts/deletion on database in that transaction should be rollback, this keeps database consistent, correct and clean.