Professional Web Applications Themes

Returning Tables - Microsoft SQL / MS SQL Server

How can I return a table when a stored proc calls another stored proc? I know the example below does not work, but it clarifies what I need. I will be able to use the returned table for use in an Update statement, so I wanted to aviod returning a cursor. IE:.... -------------------------------------- -- This is the 'called' proc.... create procedure test1 as begin select emp_id, name from personnel where status = 'A' end -------------------------------------- create procedure test2 as begin #table = exec test1 -- use #table........ end -------------------------------------- Thanks! Jason...

  1. #1

    Default Returning Tables

    How can I return a table when a stored proc calls another stored proc? I
    know the example below does not work, but it clarifies what I need. I will
    be able to use the returned table for use in an Update statement, so I
    wanted to aviod returning a cursor.

    IE:....

    --------------------------------------
    -- This is the 'called' proc....
    create procedure test1 as
    begin
    select emp_id, name
    from personnel
    where status = 'A'
    end
    --------------------------------------
    create procedure test2 as
    begin

    #table = exec test1

    -- use #table........
    end
    --------------------------------------

    Thanks! Jason


    Jason Guest

  2. #2

    Default Re: Returning Tables

    In test2:

    CREATE TABLE #table
    (
    emp_id INT,
    name VARCHAR(???)
    )

    INSERT #table EXEC test1

    SELECT * FROM #table

    DROP TABLE #table






    "Jason" <sd.us> wrote in message
    news:phx.gbl... 


    Aaron Guest

  3. #3

    Default Re: Returning Tables

    Hi

    Try:

    Insert into MyTable ( Col1, COl2 ) EXEC sproc

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
    ia-iz_5cl0.asp?frame=true

    or write yourself a UDF.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
    create_7r1l.asp?frame=true

    John

    "Jason" <sd.us> wrote in message
    news:phx.gbl... 


    John Guest

  4. #4

    Default Re: Returning Tables


    Awesome. I didn't catch this in the HELP, but I see it now. Thanks!

    Jason

    "Aaron Bertrand - MVP" <com> wrote in message
    news:#phx.gbl... [/ref]
    will 
    >
    >[/ref]


    Jason Guest

Similar Threads

  1. returning more than one value from a function
    By Jeremy Watts in forum PHP Development
    Replies: 3
    Last Post: April 4th, 02:14 PM
  2. Returning to a form
    By Stewart in forum ASP
    Replies: 1
    Last Post: October 8th, 03:31 AM
  3. Returning Values from a SP to asp
    By Harag in forum ASP Database
    Replies: 1
    Last Post: August 23rd, 10:26 AM
  4. how to returning a table id..
    By smen in forum ASP.NET General
    Replies: 1
    Last Post: August 10th, 02:17 AM
  5. Replies: 1
    Last Post: July 1st, 07:28 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139