Source code | SQL 2005 Adventure Works OLTP |
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] @EmployeeID [int], @Title [nvarchar](50), @HireDate [datetime], @RateChangeDate [datetime], @Rate [money], @PayFrequency [tinyint], @CurrentFlag [dbo].[Flag] WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION UPDATE [HumanResources].[Employee] SET [Title] = @Title ,[HireDate] = @HireDate ,[CurrentFlag] = @CurrentFlag WHERE [EmployeeID] = @EmployeeID; -- Test the error value. IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION; RETURN; END; INSERT INTO [HumanResources].[EmployeePayHistory] ([EmployeeID] ,[RateChangeDate] ,[Rate] ,[PayFrequency]) VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency); -- Test the error value. IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION; RETURN; END; -- OK, so commit COMMIT TRANSACTION RETURN; END; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], NULL, NULL; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid EmployeeID from the Employee table.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@EmployeeID'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@Title'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@HireDate'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@RateChangeDate'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@Rate'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@PayFrequency'; EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.', N'SCHEMA', [HumanResources], N'PROCEDURE', [uspUpdateEmployeeHireInfo], N'PARAMETER', '@CurrentFlag'; |