• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Transact SQL Server problem

In SQL Server there are no arrays, so if you want to insert an array into a table you must step through array and to call each step a stored procedure to insert in table each line from array.
How this can be avoided WITHOUT creating extended stored procedures ?
If I try to format the array as string and to pass it to an parameter of the stored procedure then the string cannot be longer than 8000, this is the size of nvarchar, varchar...
If I set the type of a column from an table to binay or image then I can insert into this column images of any size, but if I try to declare a binary or image variable they cannot have more than 8000.
How can I use this - binary/image type (if it can be used) ?
Or if someone have another solution on how to pass an array to a parameter of a stored procedure please reply (but not using extended stored procedures).
Thanks.

0
DanLazar
Asked:
DanLazar
  • 2
  • 2
1 Solution
 
SRigneyCommented:
If you are using SQL2000, then you can convert your array to an XML string and pass it to the stored procedure.  Then within the stored procedure you can use OpenXML, which will have access to the values in your xml string like it is a temp table.  This will be able to be used without being an extended stored procedure.

Also you can pass your array into the procedure the same as you do now and you can create the temp table by hand with a create table.  Then you can populate it with the values from your array by looping through them using PATINDEX.  You could then use that temp table in the actual working portion of the stored procedure.  Again without having an extended store procedure.

In order to pass values larger than 8000 characters I think you need to do an appendchunk within your code while adding it to the command object.  I've not had to deal with that though, so the best I can do is point you at a reference to it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth01_3.asp
0
 
DanLazarAuthor Commented:
Hello , thanks for comment.

Can you send me a little code ? I don't know how to work with SQL XML queries. The xml string will be passed to an parameter of the stored procedure
but the parameters should be nvarchar or varchar and this types allow only 8000 size. What if my string is longer than 8000 ?


0
 
SRigneyCommented:
if your string is longer than 8000 characters then set your stored procedure up to accept a text parameter.

Here is a link for the XML functionality.  I haven't done it in much detail so I can't guide you step by step.  I hope it helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_1cx8.asp

The following one is an article from SQL Server magazine.  It also has code samples that show ntext being used to pass in the xml string which allows for strings up to 2 GB.  But lets face it.  2GB is going to cause you a problem when passing it across a network.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/OvercomingOpenXMLHangups.asp

0
 
DanLazarAuthor Commented:
Ok, it works with ntext.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now