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

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 2

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.