?
Solved

Transact SQL Server problem

Posted on 2003-10-29
4
Medium Priority
?
546 Views
Last Modified: 2008-03-06
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
Comment
Question by:DanLazar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 9641790
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
 

Author Comment

by:DanLazar
ID: 9642327
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
 
LVL 15

Accepted Solution

by:
SRigney earned 840 total points
ID: 9642401
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
 

Author Comment

by:DanLazar
ID: 9700300
Ok, it works with ntext.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Progress
Introduction to Processes

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question