0

Problem: I have a stored procedure in SQL Server 2012 and I want to put constraints to the output so I only get relevant information.

I am using Execute. The way I see it I have two options:

  1. save the result of the execution into a table, so I can use it for different purposes

  2. put constraints to the variables in Execute so I only get the results I want

The first method is discussed here: Insert results of a stored procedure into a temporary table .

My code is (due to company information I can't share the whole thing):

create table #mtable ( .... )

Insert into #mtable
    Execute [myProcedure]

The error:

An INSERT EXEC statement cannot be nested.

I assume the error is because of the code in the stored procedure. How can I fix that problem without looking into the code for the stored procedure. Is there another way where I can save the content in a table?

My problem can also be solved by proposal #2. Is it possible for me to manipulate the output from the stored procedure with something like:

Execute [myProcedure] where variable1 > 100
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
econmajorr
  • 291
  • 1
  • 4
  • 10
  • 1
    By the error, `MyProcedure` uses `INSERT .. EXEC` itself, so you're out of luck. Your only remaining workaround if you can't change the procedure in any way (by making it a table-valued function, for example, which would really help) is to use `OPENQUERY`, but that has many caveats and pitfalls. I recommend reading [Erland Sommarskog's article on the matter](http://www.sommarskog.se/share_data.html). Bottom line: either write some client code to do it (not SQL) or crack open the stored procedure (adding the filtering to the stored procedure itself is the most efficient approach). – Jeroen Mostert Oct 04 '17 at 15:27
  • Why not pass the table name to the stored procedure and have it do the insert? – Andrew O'Brien Oct 04 '17 at 17:17
  • @JeroenMostert, can you suggest other ways to sovle my problem? – econmajorr Oct 09 '17 at 14:37
  • Petition Microsoft to allow nesting `INSERT ... EXEC` using a big bag of support money? That said, if you have that kind of money, getting a T-SQL developer who can rewrite things for you without nested `INSERT .. EXEC`s is probably more effective. There is no silver bullet or magic switch here -- you'll have to dig in and rewrite. You probably want to push search conditions down to the lowest level where possible anyway -- simply because it's more efficient. T-SQL is not a language designed for modularity and reuse. – Jeroen Mostert Oct 09 '17 at 14:53

0 Answers0