Professional Web Applications Themes

can I pass table variables to sprocs? - Microsoft SQL / MS SQL Server

Here are a couple of articles that you can peruse. http://www.algonet.se/~sommar/dynamic_sql.html http://www.algonet.se/~sommar/arrays-in-sql.html -- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Jesse Hersch" <com> wrote in message news:phx.gbl...  sproc?  would  you get  would  from  be very  than the  I  table  the  the  available  am ...

  1. #1

    Default Re: can I pass table variables to sprocs?

    Here are a couple of articles that you can peruse.

    http://www.algonet.se/~sommar/dynamic_sql.html
    http://www.algonet.se/~sommar/arrays-in-sql.html

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Jesse Hersch" <com> wrote in message
    news:phx.gbl... 
    sproc? 
    would 
    you get 
    would 
    from 
    be very 
    than the 

    table 
    the 
    the 
    available 
    am 


    Dan Guest

  2. #2

    Default Re: can I pass table variables to sprocs?

    Jesse Hersch (com) writes: 

    The temp table is only visible to the connection that creates it, so
    the risk is not that considerable.

    It is not really clear from your posting if you want to pass table data
    from the client to a stored procedure or between stored procedures. In
    the latter case, this arficle on my web site can give you some ideas:
    http://www.algonet.se/~sommar/share_data.html.

    If you want to pass data from client to an sproc, one alternative to
    a temp table is to have a permanent table which is key by a sessionid.
    We have over 20 and still counting in our database. In our case, the
    sessionid can be positive, in which case we use spid, or negative
    in which case it is a generated value. The negative numbers are good
    when you use disconnected record sets.
     

    Oh, no, you can structure your data as a string and unpack it as a
    table with out dynamic SQL. Yet another article on my web site
    discusses a whole bunch of methods:
    http://www.algonet.se/~sommar/arrays-in-sql.html.



    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: can I pass table variables to sprocs?

    those are good references, thanks.

    any idea why sql server doesn't support this though? it would be so useful
    to have, and not have to resort to the kinds of tricks in the articles you
    mention.

    -jesse

    Dan Guzman wrote:
    :: Here are a couple of articles that you can peruse.
    ::
    :: http://www.algonet.se/~sommar/dynamic_sql.html
    :: http://www.algonet.se/~sommar/arrays-in-sql.html
    ::
    :: --
    :: Hope this helps.
    ::
    :: Dan Guzman
    :: SQL Server MVP
    ::
    :: -----------------------
    :: SQL FAQ links (courtesy Neil Pike):
    ::
    :: http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    :: http://www.sqlserverfaq.com
    :: http://www.mssqlserver.com/faq
    :: -----------------------
    ::
    :: "Jesse Hersch" <com> wrote in message
    :: news:phx.gbl...
    ::: hi,
    :::
    ::: does anyone know if it is possible to pass a table variable to a
    ::: sproc?
    :::
    ::: something like this:
    :::
    ::: create procedure MySproc
    ::: (
    ::: mytable table
    ::: myInt int
    ::: )
    ::: as
    :::
    ::: -- now do some work with the table passed to the sproc, such as...
    ::: update foo set bar=myInt where fooid in (select * from mytable)
    ::: go
    :::
    ::: and I would call it like this:
    :::
    ::: declare MyTable table(MyID int)
    ::: insert into MyTable (MyID) values (1)
    ::: insert into MyTable (MyID) values (2)
    ::: insert into MyTable (MyID) values (3)
    ::: insert into MyTable(MyID) values (4)
    :::
    ::: exec MySproc mytable=MyTable, myInt=666
    :::
    :::
    :::
    :::
    ::: the above doesn't work, i just give it to let you all know what i
    ::: would *like* to be able to do. if you try to create the procedure
    ::: above, you get this error:
    :::
    ::: Server: Msg 156, Level 15, State 1, Procedure MySproc, Line 3
    ::: Incorrect syntax near the keyword 'table'.
    ::: Server: Msg 137, Level 15, State 1, Procedure MySproc, Line 9
    ::: Must declare the variable 'myInt'.
    ::: Server: Msg 137, Level 15, State 1, Procedure MySproc, Line 9
    ::: Must declare the variable 'mytable'.
    :::
    ::: One other thing i can think to do would be to store the results
    ::: that i would like to pass to the sproc in a temp table and have the
    ::: sproc get them from there. but i don't like this option because
    ::: the temp table needs to be very 'local' in scope - if anyone else
    ::: inserts or selects from it other than the sproc call it is meant
    ::: for, there could be problems in my application. I would prefer to
    ::: be able to pass the results to the sproc directly in a table
    ::: variable, so the scope of the table is limited to just that call to
    ::: the sproc. make sense?
    :::
    ::: Another option is to pass a string to the sproc, something like
    ::: this: '1,2,3,4', and then use a dynamic sql statement in the sproc
    ::: to get at the values. but that s because i have to use EXEC
    ::: sp_executesql in my sproc. but at least then the values passed to
    ::: the sproc are only available to a particular sproc call, which is
    ::: the limited scope behavior that i am looking for.
    :::
    ::: any ideas are much appreciated!
    :::
    ::: thanks,
    ::: Jesse


    Jesse Guest

  4. #4

    Default Re: can I pass table variables to sprocs?

    > any idea why sql server doesn't support this though? it would be so
    useful 
    you 

    The short answer is that ANSI SQL was not intended to be a procedural
    language. Many of the procedural constructs and data types developers
    often use aren't really appropriate for a RDBMS. For example, an array
    datatype violates first normal form. However, most database vendors
    offer proprietary extensions to facilitate deploying procedural code in
    the database. These help address real-world situations where it is
    often desirable perform application processing closer to the data.
    Microsoft has announced their intentions to include support for the CLR
    in the next version of SQL Server to address this issue. See
    <http://www.microsoft.com/sql/evaluation/yukon.asp> for more
    information.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Jesse Hersch" <com> wrote in message
    news:%phx.gbl... 
    useful 
    you 
    the 
    to 


    Dan Guest

Similar Threads

  1. CF pass variables to Flash
    By lvbao in forum Coldfusion Flash Integration
    Replies: 4
    Last Post: May 8th, 10:26 PM
  2. Insert Record and pass url variables
    By brianevans5179 in forum Dreamweaver AppDev
    Replies: 6
    Last Post: March 30th, 01:46 PM
  3. pass variables when execute a Dir movie
    By cobian webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 1
    Last Post: December 8th, 12:25 PM
  4. Can one pass variables through a selector?
    By Andrew Hunter in forum Mac Programming
    Replies: 5
    Last Post: September 10th, 03:18 PM
  5. How to pass global variables between files
    By Monday in forum PERL Miscellaneous
    Replies: 3
    Last Post: June 30th, 12:28 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