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

Question

I have a stored procedure that takes a string of an array of identifiers delimited by channels and parses them. I want this to happen in a transaction, so there is no need to transfer them one at a time.

If I use varchar (max) to not limit the size of the passed argument, will this cause problems? I don’t see how the limit is hit, but I also don’t want to guess or put an arbitrary constraint on the string.

CREATE PROCEDURE myProc @IDs varchar(max) AS BEGIN ... END GO 

Answer 1

There are not many. varchar(max) behaves exactly the same as any varchar less than 8000 characters, until you go above 8000 characters. The difference between varchar(200) and varchar(max) should not be different if the actual data is less than 8000 characters. If you expect smaller inputs but cannot exclude larger inputs, varchar(max) fine.

Answer 2

I don’t know if this will cause problems, but I prefer to pass the number of arrays of several elements to sprocs using XML. This makes it clearer what data it is, and there are good SQL-XML tools for “converting” XML to a pseudo-table with which you can join. Then, translation from the XML section limited by the channel can occur outside the database.

Answer 3

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

Answer 4

I use varchar (max) when I don't know the limit (what it is for). It is always useful to set variables of a certain length, but if this is not known, it is acceptable and will not cause any problems, except that your database will be a little larger and people will be able to spoof more data into

Answer 5

TSQL is not a good string manipulation language, but from the point of view of performance (and code is wise!) You better parse the string outside the database - that's +1 to n8wrl answer . Essentially, there is nothing wrong with using varchar(max) for this.

Besides using XML like n8wrl, Table Valued Parameter can be a good option if you are using SQL Server 2008.