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.

DanLazarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanLazarAuthor Commented:
Ok, it works with ntext.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.