GeezerVison is showing github.com — SQLScriptDomSamples/SampleProc.sql at main · arvindsh/SQLScriptDomSamples

Expand file tree

SampleProc.sql

Copy path

More file actions

Latest commit

History

43 lines (38 loc) · 1.18 KB

SampleProc.sql

Copy path

File metadata and controls

43 lines (38 loc) · 1.18 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

-- Some leading comments with < and > signs to test for later

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]

@BusinessEntityID [int],

@JobTitle [nvarchar](50),

@HireDate [datetime],

@RateChangeDate [datetime],

@Rate [money],

@PayFrequency [tinyint],

@CurrentFlag [dbo].[Flag]

WITH EXECUTE AS CALLER

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION;

UPDATE [HumanResources].[Employee]

SET [JobTitle] = @JobTitle

,[HireDate] = @HireDate

,[CurrentFlag] = @CurrentFlag

WHERE [BusinessEntityID] = @BusinessEntityID;

INSERT INTO [HumanResources].[EmployeePayHistory]

([BusinessEntityID]

,[RateChangeDate]

,[Rate]

,[PayFrequency])

VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Rollback any active or uncommittable transactions before

-- inserting information in the ErrorLog

IF @@TRANCOUNT > 0 and 0 < 1

BEGIN

ROLLBACK TRANSACTION;

END

EXECUTE [dbo].[uspLogError];

END CATCH;

END;