ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @msg nvarchar(255)
	DECLARE @Cancel as int
	SET @Cancel = 0 

	/* check if the batch ID exists */
	IF (SELECT count(*) from opsBatch where batchID = @BatchID) = 0
	BEGIN
		SET @msg = 'Batch ID ' + cast(@BatchID as nvarchar(5)) + ' is not recognised'
		raiserror(@msg,1,1)
		SET NOCOUNT OFF
		RETURN -1 -- quit the routine with error: This should never happen because the data has been extracted from the batch id
	END
	
		/* if we have passed all of the above tests then we can update the batch and the lines */

	BEGIN TRANSACTION -- all pass or all fail
		UPDATE
				opsBatchRecord 
		SET		
				mailStatus = 'Exported',
				MailStatusDate = cast(convert(nvarchar(20),getdate() ,112) as datetime)
		WHERE
				BatchID = @BatchID
		UPDATE 
				opsBatch 
		SET
				Status = 'Exported',
				ExtractFileName = cast(@BatchID as nvarchar(5)) + '_' + @timestamp + '.txt'
		WHERE
				BatchID = @BatchID
	
	COMMIT TRANSACTION

	Return 99
	SET NOCOUNT OFF
END

EXEC OperationalBatchGenerateFile 1234, '20121113_104300'

DECLARE	@return_value int

EXEC	@return_value = [dbo].[OperationalBatchGenerateFile]
		@BatchID = 10111,
		@timestamp = N'20121113_114300'

SELECT	'Return Value' = @return_value

GO

EXEC ?= usp_myStoredProc ?,?