Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: inserting into table variable

    hi everyone

    I am not allowed to use temp tables in any of the stored procedures i write (not my decision). So i am trying to use table variables.

    I have several stored procedures whose results I want to store into a table variable. I read some info and I realize I cannot store this information into a table variable like this:

    insert into @all
    exec sp_getresults

    I get this error: EXECUTE cannot be used as a source when inserting into a table variable.

    is there any other way that i can store the results of a stored procedure call without using a # or a ##? Oh and I also cant create a standard table either...

    id appreciate any help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummm..no

    Sorry

    Can you create a cursor on a sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummm...no

    wasn;t that silly

    Code:
    
    USE Northwind
    GO
    
    CREATE PROC mySproc99 AS SELECT OrderId FROM Orders
    GO
    
    DECLARE myCursor99 CURSOR FOR EXEC mySproc99
    
    OPEN myCursor99
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT: What happened to the DELETE Buttons?
    Last edited by Brett Kaiser; 12-06-04 at 17:59.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This whole idea is uglier than sin, but you could probably fake it using sp_OACreate and it's kin to access an OLE object that would execute the stored procedure for you.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    EDIT: What happened to the DELETE Buttons?
    It was apparently lost in one of the recent "upgrades"... Our luck with those hasn't been exactly sterling, while we get new toys, we seem to loose some of the really nice old toys (just ask Rudy!).

    -PatP

  7. #7
    Join Date
    Feb 2004
    Posts
    88
    Quote Originally Posted by awfdml

    I am not allowed to use temp tables in any of the stored procedures i write (not my decision). So i am trying to use table variables.

    ...

    is there any other way that i can store the results of a stored procedure call without using a # or a ##? Oh and I also cant create a standard table either...
    Sounds like someone is deliberately making you jump through hoops, in order that you should discover the power of table-valued functions. From the evidence, they would seem to a good candidate for the job. You can turn your called stored procedure into a table-valued user-defined function - see BOL under:

    functions/user-defined/user-defined functions that return a table data type

    then you can:

    insert @table_variable
    select * from dbo.my_new_table_valued_function( @parameter,...)

    or you can then use the output of the function as a table in the from clause of another statement

    select A.x, A.y, B.a, B.b, B.c
    from permanent_table as A,
    dbo.my_new_table_valued_function( @parameter,...) as B
    where A.n = B.n


    HTH,

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •