Professional Web Applications Themes

Usage and Benefit of Table Variable - Microsoft SQL / MS SQL Server

Dear all, Two short question for SQL2K: 1) Can table variables be passed into / out of stored procedure as parameters, just in the same way of other generic data type? ( I can't get it done, but SQL2K books online says "All data types, ..., can be used as a parameter for a stored procedure." ~ CREATE PROCEDURE - Transact-SQL Reference) 2) What is the benefit of using table variable over temp-table? Any performance issues involved? Please reply. Thanks. John...

  1. #1

    Default Usage and Benefit of Table Variable

    Dear all,

    Two short question for SQL2K:

    1) Can table variables be passed into / out of stored procedure as
    parameters, just in the same way of other generic data type?
    ( I can't get it done, but SQL2K books online says "All data types,
    ..., can be used as a parameter for a stored procedure." ~ CREATE
    PROCEDURE - Transact-SQL Reference)

    2) What is the benefit of using table variable over temp-table? Any
    performance issues involved?

    Please reply.
    Thanks.

    John
    CDARS Guest

  2. #2

    Default Re: Usage and Benefit of Table Variable

    (1) This is not allowed.
    (2) There are lots of benefits of using table variables, some of which are:
    - A table variable behaves like a local variable. It has a
    well-defined scope, which is the function, stored procedure, or batch in
    which it is declared.
    - Table variables used in stored procedures result in fewer
    recompilations of the stored procedures than when temporary tables are used.
    - Transactions involving table variables last only for the duration
    of an update on the table variable. Thus, table variables require less
    locking and logging resources.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "CDARS" <cdarshotmail.com> wrote in message
    news:97ec88b8.0307062343.37daed6bposting.google.c om...
    > Dear all,
    >
    > Two short question for SQL2K:
    >
    > 1) Can table variables be passed into / out of stored procedure as
    > parameters, just in the same way of other generic data type?
    > ( I can't get it done, but SQL2K books online says "All data types,
    > .., can be used as a parameter for a stored procedure." ~ CREATE
    > PROCEDURE - Transact-SQL Reference)
    >
    > 2) What is the benefit of using table variable over temp-table? Any
    > performance issues involved?
    >
    > Please reply.
    > Thanks.
    >
    > John

    SriSamp Guest

Similar Threads

  1. [PHP] Regex and variable usage
    By Curt Zirzow in forum PHP Development
    Replies: 1
    Last Post: November 15th, 10:33 PM
  2. Assigning a table value from a dataset to a variable
    By blitzed2010 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 29th, 07:37 PM
  3. Insert ASP variable into SQL table
    By Mintyman in forum Dreamweaver AppDev
    Replies: 4
    Last Post: April 1st, 09:39 AM
  4. Regex and variable usage
    By Gerard Samuel in forum PHP Development
    Replies: 1
    Last Post: July 16th, 08:34 PM
  5. Design: one table or two tables, depending on usage
    By Bob in forum Microsoft SQL / MS SQL Server
    Replies: 15
    Last Post: July 9th, 10:09 PM

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