1

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

Andreas
  • 1,997
  • 21
  • 35

1 Answers1

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')
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • I don't really understand, how your approach could work. If I understood it correctly, I should change the procedure's output to XML. But then, I would have XML-Output in productive code, wouldn't I? The example from above is meant to be running "compile-time" only: Just before deploying code to production, the stored procedures should be "compiled" to an actual execution plan to check if there are any obvious errors. – Andreas Sep 14 '15 at 10:32
  • @Andreas, Create one table with a XML column. Than run your SP and let it insert one row for each call into this table. The whole result fits in one XML column. There's not output at all on run-time. After the call you can analyse all results one by one... Could this help you? – Shnugo Sep 14 '15 at 10:35
  • @Snugo: Not entirely. How do I make the SP insert this row? Can this be done by setting some parameters on sp_executesql? Or do I need to modify the SP? I cannot do the latter, since the SP should go to production without anymore changes and I do not want to have XML-Strings in production. – Andreas Sep 14 '15 at 13:00
  • @Andreas, well, I was thinking of INSERT statements within your SP. There must be some kind of SELECT statements at the moment. At least you write that there is some output... What is happening with this output at the moment? Don't you need them for any analysis? Do you write them somewhere already? – Shnugo Sep 14 '15 at 13:09
  • The SPs read information from the DB. Thus it is normally used by the business-logic in C#. The output is - normally - the essence of having the SP. – Andreas Sep 14 '15 at 13:26
  • @Andreas, What I do not understand is your "normally"... If all you want to reach is, that this SP is not executed in productive environment at all, you could add one line at the top stating something like "IF runningProductive=1 then RETURN; You'll know how to detect if this is running producitve... – Shnugo Sep 14 '15 at 13:37
  • (1/2) I think, I understand the misunderstanding now. We have >100 SPs returning record-sets that are used for the application (display data, decide, what to do, etc). Sometimes, a developer creates "compile-error-like-errors" by changing the datamodel in a way that renders the SP unusable: It is not possible to even create an execution-plan for the SP. Thus I want SqlServer to try to create exection-plans for the statements in the SPs to find these errors. After a long research I did not find a better way than just executing the SPs and see if it will be executed or not. – Andreas Sep 14 '15 at 14:14
  • (2/2) Since I want to run this "compilation by execution" from SSMS, tons of record-sets are written out. And I would like to ignore all these during the test - but not in production of course. – Andreas Sep 14 '15 at 14:16
  • 1
    @Andreas, OK, now I see what you are going to do... You might want to have a look here: http://stackoverflow.com/questions/1177659/syntax-check-all-stored-procedures – Shnugo Sep 14 '15 at 14:23
  • Heureka! This solved the issue for me. Thank you very much for your effort. I would really like to mark something as an answer. But I do not really know, how the current answer has anything to do with my question from above :-) – Andreas Sep 14 '15 at 16:36
  • 1
    @Andreas, I'm very glad to hear, that I could help you to a solution finally. As I'm always hungry for reputation points :-) I've edited my answer to make it possible for you to upvote and mark as accepted, thx in advance! – Shnugo Sep 14 '15 at 16:50