/****** Object:  StoredProcedure [updateSession] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateSession]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [updateSession];
GO
	
CREATE PROCEDURE [dbo].[updateSession]

(
	@SessionID int,
	@UserID nvarchar(100),
	@DTSDtart datetime,
	@DTSEnd datetime=null,
	@DTSActivity datetime,
	@LastChanged timestamp,
	@MachineName nvarchar(100),
	@ProcessID int,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Sessions]
		SET
			[UserID]=@UserID,
			[DTSDtart]=@DTSDtart,
			[DTSEnd]=@DTSEnd,
			[DTSActivity]=@DTSActivity,
			[MachineName]=@MachineName,
			[ProcessID]=@ProcessID
		WHERE [SessionID]=@SessionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Sessions] WHERE [SessionID]=@SessionID)
			RAISERROR('Session record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Session has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Sessions] WHERE [SessionID]=@SessionID		

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateSession Succeeded'
ELSE PRINT 'Procedure Creation: updateSession Error on Creation'
GO