Hi there,

If you are sure that the length of the parameter @deliverycenter will be less than 8000 characters then use varchar(500). Whilst varchar(MAX) will not affect the performance much, since they are stored in the database memory space rather than a pointer to data concept as in Sql Server 2000 (TEXT/NTEXT/ IMAGE datatypes). So the process time with varchar(MAX) is proportional to the size of the data you are processing . (Will not be of much difference).

So the answer to your question is "The performance will not be affected much".

declare

select * from itemdetails i where i.deliverycentercd in (@deliverycenter)

I don't understand why you using the above syntax, instead you can use a direct comparison to compare two strings.(here you are trying to perform a case sensitive compare between @deliverycenter and i.deliverycentercd).