SQL Server 2012:动态 SQL 限制(> 4000 个字符)(拆分) [英] SQL Server 2012: dynamic SQL limitation ( > 4000 chars) (split)

问题描述

我在存储过程中有这个动态 SQL:

I have this dynamic SQL in a stored procedure:

Declare @template nvarchar(max)
Declare @publishdetails nvarchar(max)

set @publishdetails= ',B.[PublishedBy]=suser_name(),
  B.[PublishedDate]=GETDATE() '

set @template='if NOT EXISTS(select * from ' +@DestinationDB+ '.[CLs] where id='+ str(@slid)+') 
insert into  ' +@DestinationDB+ '.CLs (id,slid,slversion) VALUES ( '+ str(@id)+','+ str(@slid)+','+str(@slversion)+')

update  B set 
      B.[Clientid]=A.clientid,
        --.........
      B.[CreatedDate] = A.CreatedDate,
      B.[ModifiedDate] = A.ModifiedDate,
      B.[CreatedBy] = A.CreatedBy,
      B.[ModifiedBy] = A.ModifiedBy '+@publishdetails+ --Added publishdetails
    'FROM  ' + @SourceDB + '.[CLs] as A, '+ @DestinationDB+ '.[CLs] as B
        where A.slversion = '+ str(@slversion)+' and A.id='+str(@slid) + 'B.slversion = '+ str(@slversion)+' and B.id='+str(@slid)

        print 'template is: ' + @template
exec sp_Executesql @template

当 exec sp_Executesql @template 正在执行时，它会失败.因为 @template 是 > 4000 个字符并且被截断了.我怎样才能把它分成块并以正确的方式执行?

When exec sp_Executesql @template is executing, it fails. Because @template is > 4000 chars and is truncated. How can I split it in chunks and execute it the correct way?

推荐答案

您无需将文本拆分为多个部分.您确实需要确保在您 连接字符串:

You don't need to split the text into parts. You do need to make sure that truncation doesn't occur whilst you're concatenating strings:

如果字符串连接的结果超过 8,000 字节的限制，则结果被截断.但是，如果连接的字符串中至少有一个是大值类型，则不会发生截断.

If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

因此，请确保第一次连接使用的是大值类型(因此会产生大值类型作为结果)，并且每个后续连接都应避免截断:

So, make sure that the first concatenation is working with a large value type (and thus produces a large value type as its result) and every subsequent concatenation should be saved from truncation:

set @template=CONVERT(nvarchar(max),'if NOT EXISTS(select * from ' ) + @DestinationDB + ...

(通过这种方式，您不必到处插入转换)

(In this way, you don't have to insert conversions everywhere)

这会产生一个错误:

declare @t nvarchar(max)

set @t = 'select LEN(''' + REPLICATE('A',3000) + REPLICATE('B',3000) + REPLICATE('C',3000) + ''')'

exec sp_executesql @t

这会产生结果 9000:

And this produces the result 9000:

declare @t nvarchar(max)

set @t = CONVERT(nvarchar(max),'select LEN(''') + REPLICATE('A',3000) + REPLICATE('B',3000) + REPLICATE('C',3000) + ''')'

exec sp_executesql @t

这篇关于SQL Server 2012:动态 SQL 限制(> 4000 个字符)(拆分)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！