October 14, 2020 at 8:19 pm
Hello,
Wondering if anyone has any insight into how I can do this: I have a lengthy stored proc which produces a number of metrics that I may need to use for other calculations. This stored proc uses OpenQuery to gather the data. I would like to create another stored procedure that calls this stored procedure and inserts the result set into a table for me to use/manipulate in this new stored proc.
When I try to create a table variable and then INSERT INTO this table variable with EXEC Storedprocname, I get an error of "An INSERT EXEC statement cannot be nested." From what I gather, it's due to the OpenQuery that I'm prevented from doing what I need here.
Are there other ways I can get this stored proc's data so I can do stuff with it in another stored proc? I'd much rather not have multiple copies of this lengthy stored proc to manage if I can help it, which is why I just wanted to leverage the existing stored proc data.
Any ideas?
Thanks!
October 14, 2020 at 9:22 pm
on the calling proc instead of doing an insert into define a temp table manually.
on the called proc detect and see if the temp table created on the outer proc exists insert into that table instead of returning a recordset.
that should get around the issue
October 14, 2020 at 9:25 pm
Not sure I understand.
To be clear, the proc I'm calling is being used in other reports so I can't necessarily modify it to insert the resultset into a table inside that proc (or I may have misunderstood your answer, if so let me know).
Thanks!
October 14, 2020 at 9:41 pm
You should be able to in this case because whether the called proc inserts into a table or returns the result set directly will be controlled by a new parameter in the proc. Default the parameter so that existing calls to the proc work exactly as they do today. Easiest is probably to make the parameter the name of the table to insert the result to, if specified. If NULL, return a data set as before.
I'd have to see the proc's code to be any more specific than that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2020 at 10:37 pm
you would keep the same functionality.
pseudo code - without seeing the proc this may be incorrect but should give you an idea
original code
OldProc
insert into original table from openquery...
select * from original table
new codecode
calling proc
create table #new_table
call old proc
old proc
insert into original_table from openquery...
if object_id('tempdb..#new_table') is not null
begin
insert into #new_table
select * from original table
end
else
begin
select * from original table
end
October 15, 2020 at 3:57 pm
I'm sorry guys, I'm just not seeing it for whatever reason.
So proc 1, let's call it dbo.Service_Data. Uses OpenQuery to get a bunch of data and eventually, ends in a resultset which is just a select columns from #finaltemptable. (I think I should be doing something here, inserting this into a table that's a parameter?)
Proc 2, lets call it dbo.Service_Data_Summary, would check if the table above exists and if it does then it inserts into a new table here?
Do I have that right? (Can you hear how unsure I am here? ; ) )
How can I check if a #temp table exists between one stored proc to another? I thought temp tables only exist within the execution of a stored proc and are not globally available?
October 15, 2020 at 4:13 pm
temp tables can be defined on proc A and referenced by any proc called by it
proc A calls proc B
proc B can then use the temp tables defined by A before the call.
so your order of operation, because you wish to allow the old proc to work as is if not called by your new proc is
Proc A
define temp table A1
Call proc B (original proc)
Proc B code
--- do openquery into its original table
--- if table A1 exists insert into table A1
--- else
--- return recordset
--- end
proc A (continuation)
use data from table A1 as desired
October 15, 2020 at 4:47 pm
So how do I actually do this? Here's my example sprocs:
----THIS IS AN EXAMPLE OF PROC 1:
CREATE PROC dbo.Service_Data
AS
DECLARE @SQL NVARCHAR(MAX);
BEGIN
DECLARE @DATA TABLE
(COLUMN1 INT
,COLUMN2 INT
)
SET @SQL =
'SELECT
Column1
,Column2
FROM OPENQUERY(LinkedServer, "SELECT Column1
,Column2
FROM Table
'')';
INSERT INTO @DATA
EXEC sp_executesql @SQL
END
SELECT *
INTO #Sometemptable
FROM @DATA
--...do a bunch of queries, etc...
----do I add sometihng like this?
IF Object_ID('tempdb..@MoreData') IS NOT NULL
BEGIN
INSERT INTO @MoreData
SELECT *
FROM #FinalSummarytable
END
ELSE
BEGIN
SELECT *
FROM #FinalSummarytable
END
DROP TABLE #Sometemptable,#FinalSummarytable
Here's an example of proc 2:
----THIS IS AN EXAMPLE OF PROC 2:
CREATE PROC dbo.Service_Data_Summary
AS
DECLARE @MoreData TABLE
(COLUMN1 INT
,COLUMN2 INT
,COLUMN3 INT
,COLUMN4 INT
)
INSERT INTO @MoreData
EXEC dbo.Service_Data
SELECT *
FROM @MoreData
----do more stuff with this data...etc
I did a table variable instead of temp table (Does that matter?), and is what I listed above right?
October 15, 2020 at 5:48 pm
not quite - it needs to be a temp table, not a table variable.
----THIS IS AN EXAMPLE OF PROC 2:
CREATE PROC dbo.Service_Data_Summary
AS
create table #MoreData
(COLUMN1 INT
,COLUMN2 INT
,COLUMN3 INT
,COLUMN4 INT
)
-- the exec will insert into the table itself so you do not do the insert ... exec, just the exec
EXEC dbo.Service_Data
SELECT *
FROM @MoreData
----do more stuff with this data...etc
On proc 2 you do add the check and insert into the temp table if it exists.
----do I add sometihng like this?
IF Object_ID('tempdb..#MoreData') IS NOT NULL
BEGIN
INSERT INTO #MoreData
SELECT *
FROM #FinalSummarytable
END
ELSE
BEGIN
SELECT *
FROM #FinalSummarytable
END
October 17, 2020 at 12:50 am
I just got a chance to try this out and it ...kinda worked. Proc B calling Proc A returned the dataset from Proc A with the "EXEC ProcA", but I have to get this data into a temp table so I can further manipulate/summarize it. How can I do that? I had the "Select * from #MoreData" listed in the Proc B after the EXEC statement, but it just returned an empty table as the second resultset, (after the resultset it returned from the Proc A above). Not sure how to get the EXEC resultset into an actual temp table now.
On a side note, it also did take quite a long time to finish executing, the proc B calling proc A took 1:10 to complete when just executing Proc A by itself takes only 4 sec. Not sure if there's a performance hit doing it this way or what.
October 17, 2020 at 9:09 am
I think you misunderstood what I explained or you didn't implement it correctly.
doing it the way I mentioned the temp table created on proc B will have the contents inserted by proc A and can then be further manipulated by proc B without the need to create any more temp tables.
see example below
if object_id('proc_B') is not null
drop procedure proc_B
go
create procedure proc_B
as
begin
set nocount on;
set xact_abort on;
/*
this proc is a generic use proc that can be executed in two ways
1 - calling the proc directly - when this is done it will return a record set directly to the caller
2 - calling the proc from another proc with a temp table #outertable defined prior to the call - when this is done the proc will insert into that table instead of returning a recordset
*/
if object_id('tempdb..#innertable') is not null
drop table #innertable
create table #innertable
( schemaname varchar(128)
, objectname varchar(128)
)
insert into #innertable
select top 10 sc1.name
, ob1.name
from sys.objects ob1
inner join sys.schemas sc1
on sc1.schema_id = ob1.schema_id
if object_id('tempdb..#outertable') is not null
begin
insert into #outertable select * from #innertable
end
else
begin
select 'Proc B output', *
from #innertable
end
end
go
if object_id('proc_A') is not null
drop procedure proc_A
go
create procedure proc_A
as
begin
set nocount on;
set xact_abort on;
if object_id('tempdb..#outertable') is not null
drop table #outertable
create table #outertable
( schemaname varchar(128)
, objectname varchar(128)
)
exec proc_b
/*
at this point table #outertable will contain the records created by proc_b
we can now manipulate them at will
*/
select 'Proc A output', *
from #outertable
end
go
exec proc_b
exec proc_aOctober 20, 2020 at 3:21 am
I'm still having a hard time following. Not sure why it's so challenging.
I'm going to try to replicate this with a couple of test sprocs and see if I can get it.
Thank you for your time on this, it's much appreciated.
October 22, 2020 at 12:08 am
Ok, frederico, thank you thank you thank you.
I got a chance to work through it a little bit more, creating the sprocs from your code, then updating them to reference the real data, and it worked. Apologies it took so long for me to get it, but this is great. And I learned something new at the same time.
Thank you again for your expertise and generosity!
November 5, 2020 at 3:55 pm
I'm back.
I tried recreating what I did back on 10/22 with another set of stored procs and it's not working. I literally copied what worked the first time around, changed the table names and sproc names, and it's not working right.
On the sproc B calling sproc A inside it, it's returning 2 datasets. Dataset 1 is the correct dataset I expect (from sproc A) but that dataset is going into a temp table and then doing a quick aggregation, which is returned as blank in dataset 2. I'm wracking my brain here on what could possibly be an issue and I'm not finding it.
Basic formula:
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF OBJECT_ID('tempdb..#SDRdata') IS NOT NULL
DROP TABLE #SDRdata
CREATE TABLE #SDRdata
( table data...)
EXEC [dbo].[SprocA] @SLDate, @ELDate
SELECT *
INTO #TEMP
FROM #SDRdata
select vehicle_type
,sum(payroll_hours) payroll
FROM #TEMP
group by vehicle_type
END
What am I missing here? It looks literally the same as the one I did before that worked beautifully.
SprocA runs fine by itself, returning the data like it should. But this one looks like it's just executing the stored proc and displaying the data rather than inserting it into the #temp table.
Any ideas?
EDIT: Why is it always you find your answer RIGHT AFTER you've already asked for help? The issue here wasn't with SprocB. It was with SprocA and the code to insert into the table defined in SprocB:
IF OBJECT_ID('tempdb..#SDRdata') IS NOT NULL
BEGIN...
The table name here wasn't reflecting the correct table name.
Apologies for wasting time.
November 5, 2020 at 4:06 pm
without having both procs there's nothing I can do - it may be a slight error on your SprocA that causes the table not to be populated
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply