存储过程的字符串参数的最大长度是多少？

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.

我有一个长度为1,44,000的字符串,必须作为参数传递给存储过程,该存储过程是表上的选择查询。当这是一个查询(在c#中)它的工作正常。但是,当我将它作为参数传递给存储过程时,它无法正常工作。

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

这是我的存储过程,其中我已将此参数声明为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.

这里的参数@ItemNames是一个用不同名称连接的字符串,例如'Item1','Item2','Item3'....等等。

Can anyone tell what went wrong here?

谁能告诉我这里出了什么问题?

Thanks & Regards

感谢和问候

Padma

3 个解决方案

#1

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

从数据库语法看起来它看起来像Sql Server,这些是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

字节每varchar(max),varbinary(max),xml,text或image列2 ^ 31-1

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

(即2,147,483,647)表明Sql Server会处理它,但是对于ado.net。

#2

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.

我意识到这是一个老问题,但我看到的问题不是字段限制,而是语法。问题是存储过程不会将参数视为要插入SELECT文本的字符串,而是在字面上查找字段中是否存在1M +字符串。有几种方法可以解决这个问题。

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

首先,您可以在变量中动态构建SQL,然后像这样运行它:

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:

但是,这仍然会失败,因为@ItemNames中嵌入了引号,导致生成的SQL无效。您可以使用以下命令更改@ItemNames:

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.

但我没有测试过这个。这里的想法是你在字符串文本中编写转义的单引号('')以向查询处理器发送单个单引号(')。上面的REPLACE函数查看任何单引号的文本内部,并用两个单引号替换它们。

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

一个更强大的解决方案是创建一个Split表值函数,然后用以下内容更改你的IN子句:

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.

我假设您正在处理Split函数中的嵌入式引号。我不建议只使用REPLACE删除引号,因为引号可能是保护字符串值中的逗号。

#3

Maximum you can pass 8000 characters in VARCHAR(MAX) property and 4000 characters in NVARCAHR(MAX)

最大值可以在VARCHAR(MAX)属性中传递8000个字符,在NVARCAHR(MAX)中传递4000个字符

If you want to pass more then that then you need to use User Define Table Type as a parameter.

如果要传递更多信息,则需要使用“用户定义表类型”作为参数。

Step 1: Need to create User Define Table Type.

第1步:需要创建用户定义表类型。

CREATE TYPE udtt_ItemNames AS TABLE 
(
    Item nvarchar(100)    
)

Step 2: User the udtt_ItemNames into store Procedure

第2步:将udtt_ItemNames用户存储过程

CREATE PROCEDURE [dbo].[ReadItemData](@ItemNames udtt_ItemNames readonly,@TimeStamp as DATETIME)

AS

select * from ItemData

where ItemName in (select Item from @ItemNames) AND TimeStamp=@TimeStamp

So now you need to pass Table while execute the store procedure.

所以现在你需要在执行存储过程时传递Table。

#1