Post reply

Store Provedure Calling Store Procedure

  • January 6, 2006 at 6:09 am

    #188834

    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

     

    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • January 9, 2006 at 5:08 am

    #613501

    Hi,

    Please help on it......

    Are you finding it difficult to get the solution or you not getting what I want......

     

    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • January 9, 2006 at 7:13 am

    #613525

    Use a global temp table:

    create table ##mytemp()

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 9, 2006 at 7:36 am

    #613534

    Hi,

    Thankz for your reply.

    Global #table will not work as it in Client-Sever Architecture and at time there are 4-5 users logged in accessing may be same SP.

    Please help

    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • January 9, 2006 at 8:32 am

    #613592

    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

  • January 9, 2006 at 10:17 pm

    #613724

    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