What is the maximum length of a string parameter to Stored procedure?

Question

I have a string of length 1,44,000 which has to be passed as a parameter to a stored procedure which is a select query on a table. When a give this is in a query (in c# ) its working fine. But when i pass it as a parameter to stored procedure its not working.

Here is my stored procedure where in i have declared this parameter as NVARCHAR(MAX)

------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[ReadItemData](@ItemNames NVARCHAR(MAX),@TimeStamp as DATETIME)

AS

select * from ItemData

where ItemName in (@ItemNames) AND TimeStamp=@TimeStamp

---------------------------------------------------------------------

Here the parameter @ItemNames is a string concatinated with different names such as 'Item1','Item2','Item3'....etc.

Can anyone tell what went wrong here?

Thanks & Regards

Padma

Answer 1

From the looks of the database syntax it looks like Sql Server, these are the maximum sizes of things in Sql Server.

Bytes per short string column 8,000 

Is probably the limiter.

Although:

Bytes per varchar(max), varbinary(max), xml, text, or image column 2^31-1

(i.e. 2,147,483,647) suggests that Sql Server would handle it but for ado.net.