0

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

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aggicd
  • 727
  • 6
  • 28
  • At first glance, it seems you are doing an `INSERT` or `UPDATE`. You might be able to save some characters by changing the query to use the `MERGE` functionality instead. However, if you simply have a very long list of columns, there might just not be much you can really do. The limit for sp_executesql can indeed be a major pain when dealing with dynamic sql. – SchmitzIT Dec 04 '17 at 09:17
  • @SchmitzIT can you provide with a runable example of `MERGE` based on my sample code? – aggicd Dec 04 '17 at 09:19

2 Answers2

1

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:

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)

This generates an error:

declare @t nvarchar(max)

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

exec sp_executesql @t

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
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • tried that but the same error. I get syntax error because of the truncation of the @template – aggicd Dec 04 '17 at 09:24
  • 1
    @aggicd - then there's some non-obvious from your question instance where other truncation is occurring - e.g. in the code in the question, all concatenations appear to be straightforward with no bracketing. If you have any bracketing then that mean the order of operations is different from left-to-right and so you'll have to apply the "first" conversion elsewhere. – Damien_The_Unbeliever Dec 04 '17 at 09:31
  • Please check my edited post. There was a part of a code that I didn't include the first time. @publishdetails – aggicd Dec 04 '17 at 09:43
0

I suggest to use this approach:

Declare @template nvarchar(max) = N''
set @template = @template +N'.... -- Or SELECT instead of SET

Update#1

I run this simple query on my test DB:

DECLARE @query nvarchar(max) = N'',
        @i int = 1

WHILE 1000 > @i
BEGIN
    SET @query = @query + N'SELECT @@version;'
    SET @i = @i+1
END

SELECT LEN (@query)

EXEC sp_executesql @query

I got batch with length of 16983 characters. And execution goes well - no truncation. I guess the problem is inside @SourceDB + '.[CLs] and @DestinationDB+ '.[CLs] tables. Somewhere there you got data truncation.

Try to PRINT your query and run it manually.

gofr1
  • 15,741
  • 11
  • 42
  • 52