Get multiple results from sp_executesql

Question

Please have a look at the following statement:

CREATE TABLE TableA (A INT)
CREATE TABLE TableB (B INT, C INT)
DECLARE @stmt NVARCHAR(MAX) = 'SELECT * FROM TableA; SELECT * FROM TableB'
EXEC sp_executesql @stmt

The statement generates an output of two tables. Is it possible to insert the results into two temp tables instead of outputting the result sets? Or (even better) to ignore the results entirely?

Background:

I am writing some generic code to check stored procedures for "compile-errors": It executes all reading stored procedures and checks if any errors come up (e.g. missing columns or impossible execution-plans). Thus in reality, @stmt would contain the call of a stored procedure (which cannot be splitted into two seperate statements). This is working perfectly fine, but I do not like the fact that the code outputs more than a hundred tables...

Answer 1

EDIT: After a long discussion it was this, which solved the issue: Syntax check all stored procedures?

--If interested in the discussion please read this and comments...

Your approach smells like a very bad idea...

You could think of one table "CompileErrors" with columns like a SessionID, SessionDate and one XML column "Results".

Your SP can insert all SELECT results (one row for each call) into this XML column easily by adding a "FOR XML AUTO" at the end.

With "FOR XML PATH" you can control the output in detail.

Just compare these two:

SELECT TOP 10 [name],object_id,schema_id,[type] 
FROM sys.objects 
FOR XML AUTO, ROOT('objects')

SELECT TOP 10  object_id AS [@object_id]
              ,schema_id AS [@schema_id]
              ,[type] AS [@type]
              ,[name]
FROM sys.objects 
FOR XML PATH('object'),ROOT('objects')