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

I realize this is an old question, but the problem that I see is not one of field limitation, but syntax. The problem is that the stored procedure does not treat the parameter as a string to be inserted into the SELECT text, but is literally looking for the presence of the 1M+ string in your field. There are a couple of ways to deal with this.

First, you can build the SQL dynamically in a variable, and then run it like this:

DECLARE @SQL as nvarchar(max)
SET @SQL = 'SELECT * FROM ItemData WHERE ItemName in (' + @ItemsNames + ')'
         + ' AND TimeStamp = ''' + @TimeStamp + ''''
EXEC (@SQL)

However, this will still fail, because @ItemNames has embedded quotes in it, causing the resultant SQL to be invalid. You might be able to change the @ItemNames with:

REPLACE(@ItemNames, '''', '''''')

but I haven't tested this. The idea here is that you are writing escaped single-quotes ('') in the string text to send a single single-quote (') to the query processor. The REPLACE function above is looking inside the text for any single-quotes, and replacing them with two single-quotes.

A more robust solution would be to create a Split table-valued function, then change your IN clause with something like:

WHERE ItemName IN (SELECT SplitText FROM dbo.Split(@ItemNames))

I am assuming that you are taking care of the embedded quotes within the Split function. I don't recommend just removing quotes with a REPLACE, since the quotes might be protecting commas within the string value.

Answer 2

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.