How to insert Error_Message using EXECUTE sp_executesql

Question

Using MS SQL Server I have the below code - it is being executed via a EXECUTE sp_executesql command:

Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [DestTable], [SourceFile] from [tbl_IN_Ctrl_Dtl]
),
InsertCommands as
(
-- columns from Destination table
select *,
'BEGIN TRY insert into ' + [DestTable] + '(' +
STUFF((
        SELECT ',' + [DestCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY DestCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION 
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' + 
' select ' + 
-- columns from source table
STUFF((
        SELECT ',' + [SourceCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY SourceCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
        + ' from ' + [SourceFile] 
        + '; 
        update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ '''' 
        +';
        update tbl_IN_Ctrl_Header set SourceFile_INSERT = 1 where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ '''' 
        +'; END TRY BEGIN CATCH'
        as InsertCommand
 from DistinctTables drt 
)
select @cmd = 
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

--print @cmd

EXECUTE sp_executesql @cmd

I can retrieve the error number using line:

update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''

However, how do I retrieve the actual text error message? If I use ERROR_MESSAGE I just get Null. How do I modify the above to update 'ErrMsg' to the actual text error message? I've put a BEGIN TRY at the beginning of the Insert and END TRY at the end of the UPDATE but I now receive the error Incorrect syntax near ';'.

Answer 1

Let's start simpler. To retrieve an ERROR_MESSAGE() derived during execution of dynamic SQL, outside of the dynamic SQL, one way is to use an output parameter.

SET NOCOUNT ON;

DECLARE @msg nvarchar(max);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  SET @msg = N''Error: '' + ERROR_MESSAGE();
END CATCH;';

EXEC sys.sp_executesql @sql, N'@msg nvarchar(max) OUTPUT', @msg OUTPUT;
PRINT @msg;

Result:

Error: Divide by zero error encountered.

I'm having a hard time reverse engineering all your code to determine exactly what you are trying to do with the error message, and whether you want to do that for every row that fails, inside the dynamic SQL, or what. But I do feel like you should start simpler. Get it working for one thing, then try doing all this stuff/concatenation business.

To just update a row in a table (again, in a much simpler scenario than you have in the question):

CREATE TABLE #x(msg nvarchar(max));

INSERT #x(msg) VALUES(NULL);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  UPDATE #x SET msg = ERROR_MESSAGE()
  -- WHERE... ;
END CATCH;';

EXEC sys.sp_executesql @sql;

Result:

SELECT msg FROM #x;

---------------------------------
Divide by zero error encountered.

I think your main problem is missing parens, missing underscore, or using @@ERROR instead of ERROR_MESSAGE().