October 11, 2010 at 2:31 pm
I would like to call and execute a stored procedure (Stored Procedure A)inside another stored procedure. (Stored Procedure B) How can I go about returning all of the rows/results from Stored Procedure A into variables inside Stored Procedure B?? The results from Stored Procedure A needs to get into the declared variables in Stored Procedure B to insert into a temp table so I can use the temp table for a crystal report. Thanks
October 11, 2010 at 2:40 pm
You could create a temp table in the "outer" proc, and then have the "inner" proc ( the one called by the outer) insert into it. Since the inner proc is being called by the outer, it exists in the same session and has exposure to see the temp table made by the outer proc.
Be warned that you need to be careful about table naming using this approach. Read this article before you implement it -
http://sqlblog.com/blogs/linchi_shea/archive/2010/07/15/temp-table-name-resolution-or-maybe-not.aspx
But based on what you described you are after thats probably the closest thing to what you are after.
October 11, 2010 at 2:46 pm
You also can use a cursor in the outer proc. For each output it produces, you can call the Stored proc inside the cursor. But It is not good if you have millions of data!!
October 11, 2010 at 3:06 pm
YOu may want to test this to determine if it fullfills your requirements.
No cursors, two temporary tables. By the way the major amount of the code below is from Books On Line (BOL), Subject "Inserting Rows by Using SELECT INTO "
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode
INTO #EmployeeAddresses
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID
JOIN Person.Address AS a on a.AddressID = ea.AddressID
JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;
SELECT *
INTO #Table_2
FROM #EmployeeAddresses
SELECT * FROM #Table_2
October 13, 2010 at 11:18 pm
October 14, 2010 at 8:06 am
Would I be able to add my results from the second stored procedure into a cursor in the first procedure?
Thanks
October 14, 2010 at 10:18 am
Marv-1058651 (10/14/2010)
Would I be able to add my results from the second stored procedure into a cursor in the first procedure?Thanks
Why oh Why do you want to use a cursor? Would you explain further ?
October 14, 2010 at 10:23 am
The reason I would want the results from the second procedure to go into a cursor from the first procedure, because the second procedure results are more than 1. It can be 1 record, or multiple records. If your saying a cursor is a bad idea, can I dump the results into a tmp table?
October 14, 2010 at 11:16 am
Yes, focus on making the stored proc use set-based commands rather than iterating through a result set using cursors. the database can handle data in sets much more efficiently than row-by-row. Examine the posts above for some set-based approaches for passing data from one proc to another.
October 14, 2010 at 11:49 am
This code takes the data you wish to manipulate and places it into a temporary table
SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode
INTO #EmployeeAddresses
The results of the first select are placed in a 2nd temporary table by this code:
SELECT *
INTO #Table_2
FROM #EmployeeAddresses
Now what do you need to do with or to the data in Temporary table Table_2 ?
October 14, 2010 at 1:14 pm
CELKO (10/14/2010)
I have not tried it, so i am not sure about the scoping rules:1) Declare a table variable in the Outside procedure
2) Use the Inside procedure to insert into it.
Table variables are scoped the same way as normal variables ie not visible in sub procedures. Replace table variable with temp table and that'll work.
This will not have to go to TempDB or be persisted.
I'm afraid that's nothing more than a common myth. http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2010 at 1:15 pm
CELKO (10/14/2010)
I have not tried it, so i am not sure about the scoping rules:1) Declare a table variable in the Outside procedure
2) Use the Inside procedure to insert into it.
This will not have to go to TempDB or be persisted.
Won't work, for the reason suggested. Scoping. The table variable will only be available in the outside proc, not the inside one.
FYI, table variables are instantiated in TempDB.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 14, 2010 at 2:12 pm
Can you provide rough-draft code for the outer and inner procs? It's quite likely that it can be rewritten into a single procedure that will do everything you need, and do it efficiently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2010 at 2:50 pm
The way I see it is like getoffmyfoot put it where in your outer proc you submit an insert/exec statement.
--outer proc code
CREATE TABLE #temp (columns from your inner proc)
INSERT #temp
EXEC Innerprocedure
--ONLY 1 TEMP TABLE NEEDED
October 15, 2010 at 4:26 am
The big question is: Is the inner proc used by any other process without the outer proc in question? If not, I advise following GSquared's advice and combining the two of them into one proc.
If so, the code for both procs would go a long way to help us give you a proper solution.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply