Professional Web Applications Themes

Passing arrays to SP - Microsoft SQL / MS SQL Server

I have a transaction with several steps that I need to accomplish. I'm trying to decide if I should manage that transaction from my vb.net program or put the whole thing in a single Stored Procedure. It involves an array of strings that would require numerous inserts into a table. After looking through chapter 10 in "Inside Sql Server 2000" (the only T-Sql ref I have) I don't see the capability to pass an array or a collection to Sql Server so I'm thinking I should contro the transaction from my program. Is there an array datatype in T-Sql? Can ...

  1. #1

    Default Passing arrays to SP

    I have a transaction with several steps that I need to accomplish. I'm
    trying to decide if I should manage that transaction from my vb.net program
    or put the whole thing in a single Stored Procedure. It involves an array
    of strings that would require numerous inserts into a table. After looking
    through chapter 10 in "Inside Sql Server 2000" (the only T-Sql ref I have) I
    don't see the capability to pass an array or a collection to Sql Server so
    I'm thinking I should contro the transaction from my program.

    Is there an array datatype in T-Sql? Can I pass a string array to a stored
    procedure and use logic inside T-sql to do all of the inserts?
    Thanks,
    Gary


    Gary B Guest

  2. #2

    Default Re: Passing arrays to SP

    Gary B wrote:
    > I have a transaction with several steps that I need to accomplish.
    > I'm trying to decide if I should manage that transaction from my
    > vb.net program or put the whole thing in a single Stored Procedure.
    > It involves an array of strings that would require numerous inserts
    > into a table. After looking through chapter 10 in "Inside Sql Server
    > 2000" (the only T-Sql ref I have) I don't see the capability to pass
    > an array or a collection to Sql Server so I'm thinking I should
    > contro the transaction from my program.
    >
    > Is there an array datatype in T-Sql? Can I pass a string array to a
    > stored procedure and use logic inside T-sql to do all of the inserts?
    > Thanks,
    > Gary
    [url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url]


    Bob Barrows Guest

  3. #3

    Default Re: Passing arrays to SP

    > Is there an array datatype in T-Sql?

    No.
    > Can I pass a string array to a stored
    > procedure and use logic inside T-sql to do all of the inserts?
    Yes. [url]http://www.aspfaq.com/2248[/url]


    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Passing arrays to SP

    The role of the transaction is not very clear in your question (where it
    starts, ends, and what actions it captures).

    Aside from that - You cannot pass an array to SQL. However, workarounds
    are available:
    Pass a delimited string with desired entries to SQL. You can define a
    TEXT/VARCHAR stored proc input variables, p the string, and
    construct an array/table using that string (use text for long strings
    with no pre-determined length).

    If you want, I could provide sample code. Let me know.

    ---------------------------------------
    Omri Bahat
    DB Project Manager at Riptech
    2800 Eisenhower Ave, 2nd floor
    Alexandria, VA 22314
    ---------------------------------------

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

Similar Threads

  1. Passing CF Arrays to javascript
    By dxsmith99 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 18th, 04:59 PM
  2. Passing arrays to functions
    By Eric Kincl in forum PHP Development
    Replies: 1
    Last Post: November 19th, 09:13 PM
  3. Passing Arrays
    By David P. Jessup in forum ASP
    Replies: 3
    Last Post: September 30th, 07:42 PM
  4. Passing arrays through functions
    By Henry in forum ASP
    Replies: 3
    Last Post: September 15th, 02:44 PM
  5. Passing arrays to a stored procedure
    By Aaron Bertrand - MVP in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 02:37 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