Store Provedure Calling Store Procedure
-
Hi,
I am using SQL SERVER 2000.
Under one Store Procedure "A" I am calling another Store Procedure "B".
What I want IS to store the recordset generated by Store Procedure B into a Table.
Under Store Procedure A I can use statement like:
Insert into tablename Exec SP_name @var1, @var2
But here problem is I don't know what would be the number of columns when Store Procedure B generates the result.
Something like when Store Procedure B returns the recordset the table should be created with that number of column and with rows populated inside it. I am using temporary(Hash) table. So scope of that table remain upto Store Procedure A only.
Please help
-
Use a global temp table:
create table ##mytemp()
-
You can create the temp table in the first procedure with the columns that are always in the table, or at least with a column that will just serve as a place holder. In the second procedure, you can alter the temp table to add the additional columns that are needed and then populate the table.
OR
You can use the second procedure to just build the syntax to populate the temp table. You can either pass the syntax back as an output variable, or put the syntax into a temp table created by the first proc and then executed after the second procedure finishes.
CREATE PROCEDURE procA AS
CREATE TABLE #syntax (syntaxID INT IDENTITY(1,1), strSyntax VARCHAR(8000))
EXEC procB
DECLARE @sql VARCHAR(8000), @id INT
SELECT TOP 1 @id=syntaxID, @sql=strSyntax FROM #syntax ORDER BY syntaxID
WHILE @@ROWCOUNT>0
BEIGN
EXEC (@sql)
DELETE #syntax WHERE syntaxID=@id
SELECT TOP 1 @id=syntaxID, @sql=strSyntax FROM #syntax ORDER BY syntaxID
END
CREATE PROCEDURE procB AS
DECLARE @syntaxcode VARCHAR(8000)
SELECT @syntaxcode='SELECT 1 a, 2 b INTO #temp'
INSERT INTO #syntax (strSyntax)
SELECT @syntaxcode
Hope this helps.
Brian
-
Is there at least a well-defined set of columns, say you may return any 7 of a set of 20 columns? If so, make a table that contains all of the 20 columns with NULLs as their default values. Also add one more column which is some kind of uniqueidentifier column - this uniqueidentifier is passed through to stored proc B from proc A and is used to identify the records that stored proc A executed by user 1 VS the records that user 2 would get, etc... This way you get a stable table structure which allows for concurrent access...
Having said that, you could just return the 20 columns from the stored proc without resorting to the global temp table idea
Where do you get your column names? Is it some sort of pivoting stored proc?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply