Is it a good idea to use varchar(max) as a stored procedure argument?

Question

I have a stored procedure that takes a string of a pipe delimited array of IDs and parses them out. I want to have this happen in a transaction, so don't want to pass them in one at a time.

If I use varchar(max) as to not limit the size of the argument passed in, will this cause problems? I don't see the limit being hit, but I also don't want to guess or place an arbitrary limit on the string.

CREATE PROCEDURE myProc
    @IDs varchar(max) 
AS

BEGIN
  ...
END
GO

Answer 1

I don't know if it would cause problems but I prefer to pass what amount to multi-element 'arrays' in to sprocs using XML. It makes it clearer what kind of data it is and there are nice SQL-XML tools to 'transform' the XML into a pseudo-table you can join against. Then, the translation from pipe-delimited -> XML can happen outside the DB.

Answer 2

I use varchar(max) when I don't know the limit(thats what its there for). Always good to set the variables with a certain length, but if unknown this is acceptable, and won't cause any problems, except for your database growing a bit larger, and people able to dupe larger amount of data in

Answer 3

If you are on SQL Server 2008, then I'd use a table valued parameter. If not, I always prefer to use the smallest size possible, but I don't see why MAX would cause any problems as a stored procedure parameter. If you want the parameter to be practically unlimited in length than go for MAX.

Answer 4

There's not much to it. varchar(max) behaves just like any varchar less than 8000 characters until you go above 8000 characters. There should be little to no difference between varchar(200) and varchar(max) if the actual data is less than 8000 characters. If you're expecting smaller inputs but can't rule out bigger inputs, a varchar(max) is great.

Answer 5

TSQL isn't a good string manipulation language, and performance-wise (and code wise!) you'd be better off parsing the string outside of the DB - so +1 to n8wrl's answer. Intrinsically though, there's nothing wrong with using a varchar(max) for this.

Aside from using XML like n8wrl suggested, a Table Valued Parameter might be a good option if you're using SQL Server 2008.