如何从变量运行超过 8000 个字符的 SQL 语句？

How to run a more than 8000 characters SQL statement from a variable?

提问by Rachcha

I can use the following code for tiny little queries:

我可以使用以下代码进行微小的查询：

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT * FROM myTable'
Exec @sql

The above method is very useful in order to maintain large amounts of code, especially when we need to make changes once and have them reflected everywhere.

上述方法对于维护大量代码非常有用，尤其是当我们需要进行一次更改并在各处反映时。

My problem is my query (it's only one single query) that I want to feed into the @sql variable uses more than 25 table joins, some of them on temporary table variables, incorporates complex operations and it is hence much more than 8000 characters long.

我的问题是我想提供给 @sql 变量的查询（它只是一个查询）使用了超过 25 个表连接，其中一些在临时表变量上，包含复杂的操作，因此长度超过 8000 个字符.

I wished to use TEXT data type to store this query, but MSDN shows a warning message that Microsoft is planning to remove Text, NText and Image data types from their next versions. I wish my code to run in future too.

我希望使用 TEXT 数据类型来存储此查询，但 MSDN 显示了一条警告消息，表明 Microsoft 计划从其下一个版本中删除 Text、NText 和 Image 数据类型。我希望我的代码将来也能运行。

I thought of storing this query in a separate file, but as it uses joins on table variables and other procedure-specific parameters, I doubt if this is possible.

我想将此查询存储在一个单独的文件中，但由于它使用表变量和其他特定于过程的参数的连接，我怀疑这是否可行。

Kindly tell me a method to store a large query into a variable and execute it multiple times in a procedure.

请告诉我一种将大型查询存储到变量中并在一个过程中多次执行的方法。

采纳答案by Andrea Colleoni

If you are on SQL Server 2008 or newer you can use VARCHAR(MAX)

如果您使用的是 SQL Server 2008 或更高版本，则可以使用 VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

回答by MikeTeeVee

The problem is with implicit conversion.

问题在于隐式转换。

If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to VarChar(8000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter!

When concatenating long strings (or strings that you feel could be long) alwayspre-concatenate your string building with CAST('' as nVarChar(MAX))like so:

如果您有要连接的 Unicode/nChar/nVarChar 值，那么 SQL Server 会将您的字符串隐式转换为 VarChar(8000)，不幸的是，它太愚蠢了，无法意识到它会截断您的字符串，甚至会警告您数据已被被截断了！

连接长字符串（或您认为可能很长的字符串）时，始终使用CAST('' as nVarChar(MAX))预先连接您的字符串构建，如下所示：

SET @Query = CAST('' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)
           + 'SELECT...'-- some of the query gets set here
           + '...'-- more query gets added on, etc.

What a pain and scary to think this is just how SQL Server works. :(

I know other workarounds on the web say to break up your code into multiple SET/SELECT assignments using multiple variables, but this is unnecessary given the solution above.

For those who hit a 4000 character max, it was probably because you had Unicode so it was implicitly converted to nVarChar(4000).

Explanation:
What's happening behind the scenes is that even though the variable you are assigning to uses (MAX), SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar(4000) or VarChar(8000) (depending on what you're concatenating). After it is done figuring out the value (and after truncating it for you) it then converts it to (MAX) when assigning it to your variable, but by then it is too late.

想到这就是 SQL Server 的工作方式，这是多么痛苦和可怕。 :(

我知道网络上的其他解决方法说使用多个变量将您的代码分解为多个 SET/SELECT 分配，但鉴于上述解决方案，这是不必要的。

对于那些最多达到 4000 个字符的人，这可能是因为您有 Unicode所以它被隐式转换为 nVarChar(4000)。

说明：
幕后发生的事情是，即使您分配给的变量使用 (MAX)，SQL Server 也会评估您首先分配的值的右侧，并默认为 nVarChar(4000) 或 VarChar(8000)（取决于关于您要连接的内容）。完成计算出值后（并为您截断它之后），然后在将其分配给您的变量时将其转换为 (MAX)，但到那时为时已晚。

回答by Xtian11

DECLARE @sql VARCHAR(max)
SET @sql = 'SELECT * FROM myTable'
Exec @sql

Note:

笔记：

Print(@sql)

only show the first 8000 characters!

只显示前 8000 个字符！

回答by Thit Lwin Oo

use

用

EXEC
(
  '
   --your sql script here
  '
)

回答by Dalex

Problem is because your string has limit 8000 symbols by default. To prevent this you should convert it to (N)VARCHAR(MAX)

问题是因为您的字符串默认限制为 8000 个符号。为了防止这种情况，您应该将其转换为 (N)VARCHAR(MAX)

DECLARE @sql VARCHAR(8000)
        SET @sql = CAST('SELECT * FROM myTable' AS VARCHAR(MAX))
--Check length of variable
 PRINT 'Length is: '+CAST(LEN(@sql) AS VARCHAR)+ 'symbols'
        Exec @sql

回答by BD01

You should read the answer of this post which explains extremely well the situation : SQL NVARCHAR and VARCHAR Limits

您应该阅读这篇文章的答案，它很好地解释了这种情况： SQL NVARCHAR 和 VARCHAR 限制

1. If the length x of your string is below 4000 characters, a string will be transformed into nvarchar(x)
2. If the length y is between 4000 and 8000, varchar(y)
3. If the length is more than 8000 characters, nvarchar(max)which can store up to 2GB.

1. 如果字符串的长度 x 小于 4000 个字符，则字符串将被转换为 nvarchar(x)
2. 如果长度y在4000到8000之间， varchar(y)
3. 如果长度超过8000个字符，nvarchar(max)最多可以存储2GB。

Problem is that nvarchar(max) + varchar(y) = nvarchar(max) + nvarchar(4000); SQL will convert your varchar(y)into nvarchar(y)or nvarchar(4000)if y is greater than 4000 and lesser than 8000, truncating your string !

问题是nvarchar(max) + varchar(y) = nvarchar(max) + nvarchar(4000); SQL 会将您转换varchar(y)为 ，nvarchar(y)或者nvarchar(4000)如果 y 大于 4000 且小于 8000，则截断您的字符串！

回答by Gaspa79

Well I ran to this before (in SQL 2005) and I can tell you that you have two options:

好吧，我之前（在 SQL 2005 中）遇到过这个问题，我可以告诉你，你有两个选择：

1 - Use the sys.sp_sqlexec stored procedure that can take a param of type text (IMO this is the way to go). Don't mind the warning. In SQL 2008 ntext is still supported, and if you do the varchar(max) thingy there, it will work. So basically, if you have 2008, both the text solution and the varchar(max) will work, so you will have time to change it =-). In 2012 though, only the varchar(max) will work, therefore you'll have to change it before upgrading.

1 - 使用可以接受文本类型参数的 sys.sp_sqlexec 存储过程（IMO 这是要走的路）。不要介意警告。在 SQL 2008 中仍然支持 ntext，如果你在那里做 varchar(max) 的事情，它会工作。所以基本上，如果您有 2008 年，文本解决方案和 varchar(max) 都可以使用，因此您将有时间更改它 =-)。但是在 2012 年，只有 varchar(max) 可以工作，因此您必须在升级之前更改它。

2- (This is what I did at first) Check THIS post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274and do what user "Kristen" says. Worked like a charm for me. Don't forget to pre-set them to an empty string. If you understood my post you know by now that in SQL 2008 or newer is silly to do this.

2-（这就是我最初所做的）查看此帖子：http: //www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 并按照用户“Kristen”所说的进行操作。对我来说就像一种魅力。不要忘记将它们预先设置为空字符串。如果您理解我的帖子，您现在就知道在 SQL 2008 或更高版本中这样做很愚蠢。

回答by RobPrell

There is no solution for this along the way that you are doing it. MsSql as of 2012 supports Ntext for example that allows you to go beyond 8000 characters in a variable. The way to solve this is to make multiple variables or multiple rows in a table that you can iterate through.

在您执行此操作的过程中，没有解决方案。2012 年的 MsSql 支持 Ntext，例如允许您在变量中超过 8000 个字符。解决这个问题的方法是在一个表中创建多个变量或多行，您可以循环访问。

At best with a MsSql version the max size of a variable is 8000 characters on the latest version as of when this was typed. So if you are dealing with a string of say 80,000 characters. You can parse the data into ten variables of 8000 characters each (8000 x 10 = 80,000) or you can chop the variable into pieces and put it into a table say LongTable (Bigstring Varchar(8000)) insert 10 rows into this and use an Identity value so you can retrieve the data in the same order.

在 MsSql 版本中，在最新版本中，变量的最大大小为 8000 个字符。因此，如果您正在处理一串 80,000 个字符的字符串。您可以将数据解析为 10 个变量，每个变量为 8000 个字符 (8000 x 10 = 80,000)，或者您可以将变量切成小块并将其放入一个表中，比如 LongTable (Bigstring Varchar(8000)) 在其中插入 10 行并使用标识值，以便您可以按相同顺序检索数据。

The method you are trying will not work with MsSql currently.

您正在尝试的方法目前不适用于 MsSql。

Another obscure option that will work but is not advisable is to store the variable in a text file by using command shell commands to read/write the file. Then you have space available to you beyond 8000 characters. This is slow and less secure than the other methods described above.

另一个可行但不可取的模糊选项是通过使用命令外壳命令读取/写入文件将变量存储在文本文件中。然后你有超过 8000 个字符的可用空间。与上述其他方法相比，这种方法速度慢且安全性较差。

回答by Heta77

ALTER PROCEDURE [dbo].[spGetEmails]
AS
BEGIN
    SET NOCOUNT ON;
    -- Insert statements for procedure here

    declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 10000000)

 insert @local values(cast(@p as varchar(max)))
 select   col from @local


END

回答by Eric King

I have been having the same problem, with the strings being truncated. I learned that you can execute the sp_executesql statement multiple times.

我遇到了同样的问题，字符串被截断了。我了解到您可以多次执行 sp_executesql 语句。

Since my block of code was well over the 4k/Max limit, I break it out into little chunks like this:

由于我的代码块远远超过了 4k/Max 的限制，我把它分成了这样的小块：

set @statement = '                                                                                      
update pd                               
set pd.mismatchtype = 4                             
  FROM [E].[dbo].[' + @monthName + '_P_Data] pd                             
  WHERE pd.mismatchtype is null '                                                                               
exec sp_executesql @statement                               


set @statement = 'Select * from xxxxxxx'
exec sp_executesql @statement

set @statement = 'Select * from yyyyyyy '
exec sp_executesql @statement

end

So each set @Statement can have the varchar(max) as long as each chunk itself is within the size limit (i cut out the actual code in my example, for space saving reasons)

所以每组@Statement 都可以有 varchar(max) 只要每个块本身在大小限制内（为了节省空间，我在我的例子中删去了实际代码）