如何在SQL Server 2008存储过程中使用大于4000个字符的SQL字符串变量? [英] How to use SQL string variable larger than 4000 character in SQL server 2008 Stored Procedure?

问题描述

我正在使用SQL Server2008.在存储过程(SP)中，我具有字符串类型变量.现在，我想在此变量中存储一些字符串类型的值.但是，如果字符串大小小于4000，则效果很好.但是，当大小超过4000时，它将不起作用.

宣告@SQLString NVARCHAR(MAX)

SET @ SQLString =''某些SQL SELECT statemtnt及其大小(len)大于4000 ...假设其大小为10000''

执行(@SQLString)
如果我使用
十进制@SQLString varchar(10000)
然后我发现了一些错误，例如-赋予``varchar''类型的大小(10000)超过了任何数据类型(8000)所允许的最大值.

谁能帮助我克服这个问题.

在此先感谢
Rashed

I am using SQL server 2008.In Stored Procedure (SP) I have string type variables. Now I want to store some string type value in this variable. But if the string size is less than 4000 then it works nicely. But when the size is over 4000 then it does not work.

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString=''Some SQL SELECT statemtnt and its size (len) more than 4000... Suppose its size is 10000 ''

EXECUTE (@SQLString)
If I use
DECLARE @SQLString varchar(10000)
Then I found some error like- The size (10000) given to the type ''varchar'' exceeds the maximum allowed for any data type (8000).


Can anyone to help me to overcome this problem.

Thanks in advance
Rashed

推荐答案

来自

From Frequently Asked Questions - SQL Server 2005 - VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)[^] ->

"To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters. To illustrate, here''s how it will look like:

CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO

ALTER TABLE [dbo].[VarChar10000] 
    ADD CONSTRAINT [MaxLength10000]
    CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO"

更好用的

DECLARE @SQLString text

让我知道是否有帮助


问候
灰烬

Let me know if it helps


Regards
Ashish

这篇关于如何在SQL Server 2008存储过程中使用大于4000个字符的SQL字符串变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！