CREATE PROCEDURE insertReminder
 @customerId int,
 @reminderDtm datetime,
 @textMsg varchar(2000),
 @deliveryMode char(1),
 @reminderId int OUTPUT,
 @voiceMsgFile varchar(100) OUTPUT
AS
SET NOCOUNT ON

DECLARE @err_cd int 

BEGIN TRAN

-- insert new reminder
INSERT INTO reminder VALUES( @customerId, @reminderDtm, '',  @textMsg,  @deliveryMode)
SET @err_cd=@@ERROR

IF @err_cd <> 0 
BEGIN
   ROLLBACK TRAN
   RETURN(@err_cd)
END

-- get customer_id just created
SET @reminderId=@@IDENTITY
SET @voiceMsgFile='c'+@customerId+'r'+@reminderId+'.wav'

-- Update new reminder with file
UPDATE reminder set voiceMsgFile=@voiceMsgFile WHERE reminderId=@reminderId
SET @err_cd=@@ERROR

IF @err_cd <> 0 
BEGIN
   ROLLBACK TRAN
   RETURN(@err_cd)
END

COMMIT TRAN

-- get value and go home
SELECT reminderId, voiceMsgFile FROM reminder WHERE reminderId = @reminderId
GO