February 24, 2011 at 11:20 am
Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure. My question is
1) How to work-a-round this
2) Anyone know why this limitation even exists?
I've always used the "Create Temp Table - Store SP Results In Temp table - Return Temp Table " approach to get greater control (i.e sorting, column order, column selection, results filtering) over the results from a Stored procedure and its always worked. Now I run into this limitation because I am for the first time trying to make a SP call from within an SP call.
My inner SP calls the xp_CmdShell Extended SP. This SP I created works great and I would like the ability to call it and do so many times and take the results from it and place them in a temp table and this is where I ran into this error.
I could take the code in my SP that uses the xp_CmdShell Ext SP and put it in this new outer SP but this doesn't make sense from a logical perspective. It's like reinventing the wheel. While SSQL is set based and traditional programming langauges are not it just seems illogical you can't call an SP from another SP.
So how do you get around this limitation other then recreating the SP so that your outer SP contains all the code your Inner SP did?
Thanks
Kindest Regards,
Just say No to Facebook!March 2, 2011 at 3:12 pm
Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure.
No, that is not what it is telling you.
It is telling you is that you cannot have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 2, 2011 at 3:18 pm
Erland has been around the SQL block a few times and he makes some pretty good points as to why not to use INSERT-EXEC as a general development technique:
http://www.sommarskog.se/share_data.html#INSERTEXEC
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2011 at 1:53 pm
I'm all for BEST PRACTICES so if there is a better way to do this then using the INSERT-EXEC method I'm open to trying it.
I use this now to call xp_CmdShell to run a Powershell command to obtain the the last time a system, not the SQL Server Engine instance, rebooted. If there is a way in T-SQL to do this I'd love to hear about. I spent a decent bit of time trying to avoid using a powershell script and stay within native T-SQL but all 'last start' solutions returned when TempDB was created or when the server engine restarted .
Ideas?
Thanks
Kindest Regards,
Just say No to Facebook!March 15, 2011 at 2:15 pm
I would setup a SQL Agent job that was scheduled to "Start automatically when SQL Agent starts" to call your PowerShell script, retrieve the "last reboot time" from the OS and persist that value in a configuration table somewhere...then you do not need xp_cmdshell or INSERT-EXEC, you can just grab the value when you need it using standard T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2011 at 2:32 pm
To clarify...I am implying that you would enhance your PowerShell script to update the configuration table in the database with the "last reboot time".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 30, 2011 at 7:50 am
Ok, so here's something weird to throw into the mix.
I found this thread because of the error when I was trying to run the results of this query in SQL 2005.
INSERT INTO#temp_subscriberDetails
EXEC sp_helpsubscription (select db_name())
I get the error message.
An INSERT EXEC statement cannot be nested.
But the query DOES insert the records into my table.
I thought I would try running this instead to get round the error message:
BEGIN TRY
INSERT INTO#temp_subscriberDetails
EXEC sp_helpsubscription (select db_name())
END TRY
BEGIN CATCH
END CATCH
When I run this no error message is displayed (which I understand) but no records are inserted into the table, which I don't understand.
September 30, 2011 at 10:48 am
If you look at the code for sys.sp_helpsubscription you'll notice it calls two procs, sys.sp_MSrepl_getpublisherinfo and sys.sp_MSrepl_helpsubscription. Chances are the proc sys.sp_MSrepl_getpublisherinfo does not make use of INSERT EXEC and is successfully returning a resultset without throwing an error, which you are capturing in your temp table. The error is likely generated out of the second proc call sys.sp_MSrepl_helpsubscription so while you get data in your temp table, the call to sys.sp_helpsubscription results in an error.
As to why it does not work this way when used inside a try/catch, that's interesting. I can confirm all behavior you're describing on SQL 2005 Standard build 9.00.4060.00.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 30, 2011 at 12:57 pm
opc.three (3/15/2011)
To clarify...I am implying that you would enhance your PowerShell script to update the configuration table in the database with the "last reboot time".
I understand. Thanks
Kindest Regards,
Just say No to Facebook!August 1, 2016 at 10:20 am
For what it is worth, we have gotten around this by having the called SP return a text string containing the offending SQL code to the calling SP, then executing the code WITHIN the calling SP, rather than calling on the second SP to do the EXEC.
Further, so that the subordinate SP can be called directly for its data output, or called by another SP for further use of the data, we configure it with an output variable as a switch. If we call it with the output variable NULL, it EXECUTES the internal SQL and passes back the data (e.g., to an ASP.NET request). If we need to use it in another SP, we we pass an output variable to it, and get back the SQL text that we then execute inside the other SP.
Here is a simple example which uses a table tblDevices which has fields Device_Id (int) and FirstName (varchar(30)) amongst other things. There is a record with Device_Id = 10275.
Subordinate SP (@r is the "switch")
CREATE PROCEDURE [dbo].[sp_TestNested1] (@Device_ID Int, @r VARCHAR(MAX) out)
AS
BEGIN
DECLARE @sSql VARCHAR(MAX)
SELECT @sSql = 'DECLARE @Table TABLE(FirstName VARCHAR(30))
BEGIN
INSERT INTO @Table(FirstName) (SELECT FirstName FROM tblDevices WHERE Device_Id = ' + CAST(@Device_Id AS VARCHAR(10)) + ') END
SELECT * FROM @Table'
END
BEGIN
IF @r IS NULL
BEGIN
EXEC(@sSql)
END
ELSE
BEGIN
SELECT @r = @sSql
END
END
Calling SP
CREATE PROCEDURE [dbo].[sp_TestNested2] (@Device_ID Int)
AS
BEGIN
Declare @r VARCHAR(Max)
EXEC dbo.sp_TestNested1 10275, @r OUTPUT
EXEC(@r)
END
--------------------------------
So, if we
EXEC dbo.sp_TestNested1 10275,null, it executes the SQL and we get back the results
If we EXEC dbo.sp_TestNested2 10275, it passes the @r switch, gets back the SQL in @r, and executes the dbo.sp_TestNested1 code internally.
At the end of the day, we have a single object in dbo.sp_TestNested1 that can either be executed itself, or used by any number of other SPs that need to do something with its results without duplicating the dbo.sp_TestNested1 code.
October 18, 2019 at 1:50 pm
This was removed by the editor as SPAM
January 20, 2020 at 8:58 am
This was removed by the editor as SPAM
January 21, 2020 at 8:59 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply