Professional Web Applications Themes

table data type in Stored Proc - Microsoft SQL / MS SQL Server

Does some one have a snippet that shows how to declare and define a table data type? Is it possible to use a table data type to aggregate the result sets returned by a stored proc and then select * from the table? Thanks!...

  1. #1

    Default table data type in Stored Proc

    Does some one have a snippet that shows how to declare
    and define a table data type?

    Is it possible to use a table data type to aggregate the
    result sets returned by a stored proc and then select *
    from the table?

    Thanks!
    Evan Guest

  2. #2

    Default Re: table data type in Stored Proc

    Evan,

    DECLARE
    employee TABLE(empid INT,empname VARCHAR(20))

    No.You cannot do something like:
    INSERT INTO employee EXEC <stored procedure>

    Try a temp table or use a table-valued function.

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Evan Bonnett" <com> wrote in message
    news:0de101c36729$3f5dae50$gbl... 


    Dinesh.T.K Guest

  3. #3

    Default Re: table data type in Stored Proc

    >> Is it possible to use a table data type to aggregate the result sets
    returned by a stored proc and then select * from the table? <<

    The short answer is that you have to use a base or temp table, or
    perhaps a higly proprietary table valued function.

    The better answer is that you need to start thinking in terms of
    non-procedural code, sets and predicates, instead of imitating a 1950's
    punch card or magnetic tape system, with each procedural step producing
    a working file that was passed along to the next step in the process.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: table data type in Stored Proc

    >> Can I take a "query that uses a cursor" [sic] and returns multiple
    result sets (based on the # of members in the cursor) and combine the
    resultsets in the table within the same procedure? <<

    A cursor and a query are totally different things. Queries return
    entire sets all at once. Cursors imitate a sequential file system,
    working with one record at a time. What does "members in the cursor"
    mean? The row count or something?

    This sounds like you want a simple "INSERT INTO Foobar SELECT ... FROM
    .. WHERE ...;" statement. Think about data in sets not in records and
    sequential files.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Import huge data quickly...DTS/ Asynch stored proc....
    By Samir in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 9th, 03:44 AM
  2. Data From INSERT In Stored Procedure Does Not Appear In Table
    By Leonard in forum Microsoft SQL / MS SQL Server
    Replies: 15
    Last Post: August 5th, 11:03 PM
  3. Replies: 1
    Last Post: July 15th, 01:07 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