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 ';'.
Best Answer
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.Result:
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):
Result:
I think your main problem is missing parens, missing underscore, or using
@@ERROR
instead ofERROR_MESSAGE()
.