passing comma delimited string

Question

passing comma delimited string

amackay_1

Ten Centuries

Points: 1240
More actions
September 6, 2007 at 7:21 am

#374138

Hi there, I have created a system that allows the user to enter a comma delimited string of codes. This list is passed to a stored procedure in a Varchar(max) variable, where processing is completed on the list - I'm using a split function to put each value in the comma-delimited string into a row in a temporary table, do some processing and then the results are returned. They want to increase the capacity of the system past the limit of 8000 characters that varchar(max) allows, but the Text type has limited functionality when it comes to manipulation of the values it contains. I need to be able to use the LEFT, LEN functions etc. on the string in the function that splits the string into rows in the temporary table. Ideally they would like to allow unlimited length of the comma-delimited string. Any ideas on an alternative methods to acheive this?
Thanks,
Andrew

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Eisbrener SSC-Addicted Points: 489 More actions · Answer 1

I'm assuming your "system" is an application sitting on top of SQL. If this is correct, the quickest solution would be to parse the string within the app while inserting each value into a DataTable which you can then pass to SQL as an XML file. Use SQL to parse the XML into the temp table and continue as normal. This would eliminate any constraints imposed by the SQL VARCHAR data type.

John

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Anders Pedersen

SSChampion

Points: 11410

September 6, 2007 at 12:27 pm

#731947

varchar(max) is not limited to 8000 char.

David Tyler-455966 SSC Veteran Points: 298 More actions · Answer 3

This is true, max indicates that the maximum storage size is 2^31-1 bytes. You should have no trouble passing very large strings of data.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

SQL has an input buffer limit. I don't know if it is a set size or if it is based on the available server resources, but if the length of the command you send it, you get a heck of a set of errors. So, if you are using a VARCHAR(MAX) data type, make sure your developers are properly using a command object and parameter objects rather than simply executing a string.

That being said, even with a good parsing routiine, you are doing something that will get exponentially slower as the string gets longer. If you are doing this because you regularly expect a very large string being passed in, change the process to either have the developers create a populate a temp table and then run a procedure against it, or use a typed XML parameter. SQL 2005 XML parsing is pretty fast and much better suited to what you are doing.

amackay_1 Ten Centuries Points: 1240 More actions · Answer 5

Thanks guys, somehow I thought that Varchar(Max) variable had an 8000 character limit, but that's great that it doesn't. The original issue was discovered when I had the variable set to Varchar(4000), and I had already switched to Varchar(Max) and assumed the problem would still occur. But I've now tested the system out with 20,000 codes (about 100,000 characters in the string) and it is working fine.

Thanks again,

Andrew

noeld SSC Guru Points: 96590 More actions · Answer 6

I've now tested the system out with 20,000 codes (about 100,000 characters in the string) and it is working fine.

I believe you are heading on the wrong path if you need to scale. You should have a table on the server with categories and you should be able to run selects by categories and then join that to your search. But that's just my opinion, maybe the procedure won't be used past those values and you will be just fine.

* Noel

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

I agree!

If you are calling the procedure, why are you passing a string. Why not just insert the values into the table directly? To say it kindly... This is not a very ideal way to do this. Is this some black box code that you can't touch?

At the very least, change the comma delimited string to XML.

amackay_1 Ten Centuries Points: 1240 More actions · Answer 8

There are a couple of factors that led to the current setup -

1. The system was originally designed to handle a couple of hundred codes but the requirements ballooned after a number of revisions.

2. The requirements were for the user to be able to enter the codes in a comma delimited string - they can type them into a text box directly or copy and paste from Excel - the tabs from the Excel input are then replaced with commas.

That being said, it is rare that the system is used to check more than a couple of hundred codes but the marketing now promotes the ability to check "thousands" at a time, so the client is happy with the solution using the varchar(Max) variable.

Given what the system has turned into a different approach would be ideal, but this work-around is sufficient given the history and usage patterns.

Thanks again,

Andrew