SQL 存储过程的字符串参数的最大长度是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2712336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the maximum length of a string parameter to Stored procedure?
提问by padmavathi
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 的字符串，它必须作为参数传递给存储过程，该过程是对表的选择查询。当在查询中给出 this 时（在 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
帕德玛
采纳答案by amelvin
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。
回答by Kevin
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:
更强大的解决方案是创建一个拆分表值函数，然后使用以下内容更改您的 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 删除引号，因为引号可能会保护字符串值中的逗号。
回答by Shiraj Momin
Maximum you can pass 8000 characters in VARCHAR(MAX)property and 4000 characters in NVARCAHR(MAX)
您最多可以在VARCHAR(MAX)属性中传递 8000 个字符，在属性中传递4000 个字符NVARCAHR(MAX)
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 进入 store 过程
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 。
