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 ';'.

• Aaron Bertrand: I think you're biting off way too much in one shot. Try to solve the problem for a single, hard-coded insert command first, then jump to adding error handling, then jump to combining multiple commands.

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().

• Michael: I'm trying to write the error message to table using the following line - I just need to change @ERROR to ERRORMESSAGE: update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''