This allows the second procedure to be used one of two ways. It can be read in the parent process after the population. The first option is to integrate the two stored procedures together into a single stored procedure. John Join Tek-Tips ® Today! Without this information, we could only keep on guessing. Hi all I understand why I'm getting this error but do I overcome it? This can be beneficial to other community members reading this thread. My other option was that I was going to copy the state level proc to a new one and add an outer loop to loop through all the states.
Apologies in advance because I must be being dumb, but can't you just remove 'StoredProc2' as it is only getting the output from 'StoredProc1'? The tbl variable is declared and used in the child process, it is not visible to the parent process. Chances are the proc sys. Any help will be appreciated greatly! No, that is not what it is telling you. Believe me, in may case SoredProc2 is necessary, because it is calling StoredProc1 with different parameters, till I get the result I wanted from StoredProc1. Thanks Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure. The first procedure does not perform an insert exec. My question is 1 How to work-a-round this 2 Anyone know why this limitation even exists? Ok, so here's something weird to throw into the mix.
You cannot change the state of the database from a function, you can do it from a stored procedure. Thanks i'm a little late to this ballgame; i came across this while i was researching a project. How can I achieve this? TechnicalUser Warlock The sql contains a sql statment that executes a stored procedure. Perhaps somebody has a better one. Thats the nesting its complaining about. You also asked the question as to why you were getting table already exists for the temporary table.
TechnicalUser 9 Jan 03 05:27 John, A couple of things I see at first glance is that you seem to have an insert statement that doesn't insert any values. For pricing information, this might be done by an order entry client windows app or a web page. If in the any temporary table is used, it does not work. A table-valued user-defined function can be used to replace stored procedures that return a single result set. Please explain, what is a source of the problem? Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure. It can be used remotely, whereby it returns standard query results. From the example I just gave, this is the conclusion.
The error then is avoided. Or maybe there are other options to workaround? That part is fine as I often execute dynamic sql. Still in the second procedure, we execute our insert exec into PriceResults. Other options you could use are: global temporary tables - although I've yet to use one yet. We've had the nested insert problem in a number of places in our stored procedures. Once the session is stopped the temp table is dropped automatically.
I want to avoid changing the called procs at all costs as they work with formulas, calculations, cursors etc and took awhile to get bedded down. . It works for me the below solution, check and see if that works for you also. It's easy to and it's free. The insert is used to store the result returned from the called procedure. Calling the second stored procedure and inserting the result into a table variable will generate this error.
Thanks Não encontrei a solução no link informado, podem apontar por favor. Após isso dá merda se chamo o record set. It creates a temp table with a specific name e. Also if I'm not mistaken local temporary tables are only available thru the current session. It's like reinventing the wheel.
Please tell, is there any workaround for my case? Precisei fazer uma procedure que trabalha com duas tabelas temporárias e depois faço uma pivot table. This option is possible if there are no other stored procedures, scripts or applications that are using either of the stored procedures to be merged. I have read many of the recommended articles and I don't think they will address this issue. The name was changed at execution time. Check the below blog posts as suggested earlier in this thread by Uri Dimant.
That is not possible if any kind of database change is involved. Join your peers on the Internet's largest technical computer professional community. The proc uses a cursor as well as 2 temp tables, 1 outside the cursor and another within. I've been microsoft support and read the articles there but I didn't find a solution. At the end of the day, we have a single object in dbo. Because of the error I think that the session is not being closed and therefore I get the error. Thank you for quick answer! It can also be used by another stored procedure that knows to create the temp table.
Because I am nesting Insert Exec statements. Since I don't, would there be anything similar to table types as parameters? Otherwise, we do nothing, which leaves the data in the temp table for the calling procedure to use. If logic ever changed, I'd have to change it in two places. Adv: You can suppress the ConnectionString. Any comments would help, any ideas about nesting these statements, or alternative solutions. Is there a simple approach I can employ based on what I have shown here i.