BEGIN TRY
    SELECT  3 / 0
END TRY
BEGIN CATCH
    DECLARE @errornumber INT
    DECLARE @errorseverity INT
    DECLARE @errorstate INT
    DECLARE @errormessage NVARCHAR(4000)
    SELECT  @errornumber = ERROR_NUMBER() ,
            @errorseverity = ERROR_SEVERITY() ,
            @errorstate = ERROR_STATE() ,
            @errormessage = ERROR_MESSAGE()

    SELECT  @errornumber ,
            @errorseverity ,
            @errorstate ,
            @errormessage

    RAISERROR (
             @errormessage, -- Message text,
             @errorseverity,                        -- Severity,
             @errorstate,                         -- State,
           @errornumber
          );
END CATCH
View Code

CREATE Proc [dbo].[UCreateOrAppendTextFile](@Filename VarChar(100),@Text nVarchar(4000))
AS
DECLARE @FileSystem int
DECLARE @FileHandle int
DECLARE @RetCode int
DECLARE @RetVal int
DECLARE @CreateOrAppend int

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
RAISERROR ('could not create FileSystemObject',16,1)

EXECUTE @RetCode = sp_OAMethod @FileSystem , 'FileExists', @RetVal out, @FileName
IF (@@ERROR|@RetCode > 0)
RAISERROR ('could not check file existence',16,1)
-- If file exists then append else create
SET @CreateOrAppend = case @RetVal when 1 then 8 else 2 end
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @Filename, @CreateOrAppend, 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR ('could not create File',16,1)

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @text
IF (@@ERROR|@RetCode > 0 )
RAISERROR ('could not write to File',16,1)

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close'
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not close file ',16,1)

EXEC sp_OADestroy @filehandle
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not destroy file object',16,1)

EXEC sp_OADestroy @FileSystem
----------------------------------------