Stored Procedure That Will Create and Update Table

Below is a stored procedure code that will create or update the data. Its a prototype stored procedure for updating and inserting record to a certain database.

Stored Procedure that will Update or Create Data

Create Proc [dbo].[FrtCnt_sp_CreateUpdateFactor]

@pk INT, -- primary key

@fkpackstyle INT, -- foriegn key for packstyle

@descpn CHAR(25), -- description

@coef float   -- coeffient

AS

      DECLARE @ReturnValue int

      DECLARE @ErrorCode int

      DECLARE @nCnt int

      SET @nCnt = 0

/*Determine if the current pk being passed to @pk currently exist in the recordset*/

SELECT a.pkfactor FROM frtcnt_tbl_ref_factor a WHERE a.pkfactor = @pk

IF @@rowcount >0

      SET @nCnt =1 –set

BEGIN TRANSACTION

            -- Insert New Record to the database

            IF(@nCnt =0)

                  BEGIN

                        INSERT INTO  frtcnt_tbl_ref_factor

                        (

                                    pkfactor,

                                    fkpackstyle,

                                    descpn,

                                    Coef

                        )

                        VALUES

                        (

                              @pk,

                              @fkpackstyle,

                              @descpn,

                              @Coef

                        )

                        SET @ErrorCode = @@ERROR

                        IF (@ErrorCode <> 0) GOTO DetailError

                        SELECT @ReturnValue = SCOPE_IDENTITY()                     

                  END

            ELSE

                  BEGIN

                        -- Update The Existing Record in the Database

                        UPDATE  frtcnt_tbl_ref_factor SET

                                    pkfactor = @pk,

                                    fkpackstyle = @fkpackstyle,

                                    descpn = @descpn,

                                    Coef = @coef,

                                    datemodified = getdate(),

                                    modifiedby =  suser_sname()

                 

                        WHERE pkfactor = @pk

                        SET @ErrorCode = @@ERROR

                        IF (@ErrorCode <> 0) GOTO DetailError                      

                  END

COMMIT TRANSACTION

      RETURN (0)

      DetailError:

      ROLLBACK TRANSACTION

      RETURN(@ErrorCode)