I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get the return value from the procedure.
This is a simplified version of what I've got:
DECLARE @errorLogId INT DECLARE @otherDbName NVARCHAR(MAX) = 'Foo' -- Get the database name from somewhere DECLARE @sql NVARCHAR(MAX) SET @sql = @otherDbName + '.dbo.SomeProc' -- I want @errorLogId to be the return value from SomeProc, this didn't work. EXECUTE @errorLogId = sp_executesql @sql IF @errorLogId <> 0 RAISERROR('SomeProc failed, ErrorLogId = %d.', 16, 1, @errorLogId) WITH SETERROR
If an error occurs in SomeProc it is caught and an entry is written to an error table (in the other database), then SomeProc returns the error table ID (identity) of the record that was written. Otherwise SomeProc returns 0.
I know SomeProc is failing because an error is written to the error log in the other database, but in the local database @errorLogId is 0 which is treated as success. I thought the return value would be 'passed through' sp_executesql, but I think the return value is actually from sp_executesql not SomeProc (i.e. sp_executesql can be successful regardless of whether SomeProc succeeded or failed).
Is there a way to access the return value from @otherDbName.dbo.SomeProc in the local database?