USE [byrndb]
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateJob]    Script Date: 10/04/2012 12:31:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:
-- Create date: October 3, 2012
-- Description:	Update a job
-- =============================================
ALTER PROCEDURE [dbo].[sp_UpdateJob] 
	-- Add the parameters for the stored procedure here
	@ID int = 0,
	@JobNumber		varchar(20) = NULL,
	@Description	varchar(MAX) = NULL,
	@Location		varchar(MAX) = NULL,
	@SubdivisionID	int = 1,
	@Section		varchar(50) = NULL,
	@LotBlk			varchar(8) = NULL,
	@FbPg			varchar(9) = NULL,
	@MemoInfo		varchar(MAX) = NULL,
	@EmployeeID		int = 1,
	@VolPg			varchar(9) = NULL,
	@Estimate		decimal(10,2) = 0.00,
	@Amount			decimal(10,2) = 0.00,
	@FndDate		datetime2(7) = NULL,
	@AssignDate		datetime2(7) = NULL,
	@CompletionDate	datetime2(7) = NULL,
	@result int = 0 output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS (SELECT * FROM jobs WHERE JobID = @ID)
	  BEGIN
	    -- Check for change and update JobNumber
	    IF (SELECT JobNumber FROM jobs WHERE JobID = @ID) != @JobNumber
	      BEGIN
	        UPDATE jobs SET JobNumber = @JobNumber WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Description
	    IF (SELECT JobDescription FROM jobs WHERE JobID = @ID) != @Description
	      BEGIN
	        UPDATE jobs SET JobDescription = @Description WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Location
	    IF (SELECT JobLocation FROM jobs WHERE JobID = @ID) != @Location
	      BEGIN
	        UPDATE jobs SET JobLocation = @Location WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Subdivision
	    IF (SELECT SubdivisionID FROM jobs WHERE JobID = @ID) != @SubdivisionID
	      BEGIN
	        UPDATE jobs SET SubdivisionID = @SubdivisionID WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Section
	    IF (SELECT JobSection FROM jobs WHERE JobID = @ID) != @Section
	      BEGIN
	        UPDATE jobs SET JobSection = @Section WHERE JobID = @ID
	      END
	      
	    -- Check for change and update LotBlk
	    IF (SELECT JobLot_blk FROM jobs WHERE JobID = @ID) != @LotBlk
	      BEGIN
	        UPDATE jobs SET JobLot_blk = @LotBlk WHERE JobID = @ID
	      END
	      
	    -- Check for change and update FbPg
	    IF (SELECT JobFb_pg FROM jobs WHERE JobID = @ID) != @FbPg
	      BEGIN
	        UPDATE jobs SET JobFb_pg = @FbPg WHERE JobID = @ID
	      END
	      
	    -- Check for change and update MemoInfo
	    IF (SELECT JobMemo_info FROM jobs WHERE JobID = @ID) != @MemoInfo
	      BEGIN
	        UPDATE jobs SET JobMemo_info = @MemoInfo WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Employee
	    IF (SELECT EmployeeID FROM jobs WHERE JobID = @ID) != @EmployeeID
	      BEGIN
	        UPDATE jobs SET EmployeeID = @EmployeeID WHERE JobID = @ID
	      END
	      
	    -- Check for change and update VolPg
	    IF (SELECT JobVol_pg FROM jobs WHERE JobID = @ID) != @VolPg
	      BEGIN
	        UPDATE jobs SET JobVol_pg = @VolPg WHERE JobID = @ID
	      END
	    
	    -- Check for change and update Estimate
	    IF (SELECT JobEstimate FROM jobs WHERE JobID = @ID) != @Estimate
	      BEGIN
	        UPDATE jobs SET JobEstimate = @Estimate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update Amount
	    IF (SELECT JobAmount FROM jobs WHERE JobID = @ID) != @Amount
	      BEGIN
	        UPDATE jobs SET JobAmount = @Amount WHERE JobID = @ID
	      END
	    
	    -- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = @FndDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = @AssignDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = @CompletionDate WHERE JobID = @ID
	      END
	    
	    SET @result = 1
	  END
	ELSE BEGIN SET @result = 0 END
END

RETURN @result

USE [byrndb]
GO

DECLARE	@return_value int,
		@result int

EXEC	@return_value = [dbo].[sp_UpdateJob]
		@ID = 6692,
		@JobNumber = N'99999',
		@Description = N'NOTHING',
		@Location = N'something',
		@SubdivisionID = 90,
		@Section = N'2',
		@LotBlk = N'2/2',
		@FbPg = N'2/2',
		@MemoInfo = N'NOTHING',
		@EmployeeID = 21,
		@VolPg = N'2/2',
		@Estimate = 2.00,
		@Amount = 2.00,
		@FndDate = 2012-10-03 00:00:00.00,
		@AssignDate = 2012-10-03 00:00:00.00,
		@CompletionDate = 2012-10-03 00:00:00.00,
		@result = @result OUTPUT

SELECT	@result as N'@result'

SELECT	'Return Value' = @return_value

GO

-- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = @FndDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = @AssignDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = @CompletionDate WHERE JobID = @ID
	      END

-- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = '@FndDate' WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = '@AssignDate' WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = '@CompletionDate' WHERE JobID = @ID
	      END

EXEC	@return_value = [dbo].[sp_UpdateJob]
		@ID = 6692,
		@JobNumber = '99999',
		@Description = 'NOTHING',
		@Location = 'something',
		@SubdivisionID = 90,
		@Section = '2',
		@LotBlk = '2/2',
		@FbPg = '2/2',
		@MemoInfo = 'NOTHING',
		@EmployeeID = 21,
		@VolPg = '2/2',
		@Estimate = 2.00,
		@Amount = 2.00,
		@FndDate = '2012-10-03 00:00:00.00',
		@AssignDate = '2012-10-03 00:00:00.00',
		@CompletionDate = '2012-10-03 00:00:00.00',
		@result = @result OUTPUT