1

In SQL Server I have dynamic sql query, but the size of the query is higher than 4000 chars. Like in this post SQL Server 2012: dynamic SQL limitation ( > 4000 chars) (split) there is no problem to manage with this issue. But the problem is when I want to join string with variable. For example, this works ok:

DECLARE @sqlQuery NVARCHAR(MAX);
DECLARE @sqlQueryWhere NVARCHAR(MAX);

SET @sqlQuery =
    CONVERT(nvarchar(max),'SELECT
 .... '
EXECUTE sp_executesql @sqlQuery

But this sample doesn't work:

 DECLARE @sqlQuery NVARCHAR(MAX);
 DECLARE @sqlQueryWhere NVARCHAR(MAX);

SET @sqlQueryWhere = CONVERT(nvarchar(max),' 1 = 1 ' )

SET @sqlQuery =
        CONVERT(nvarchar(max),'SELECT
     .... ' + @sqlQueryWhere + ' group by ... '
EXECUTE sp_executesql @sqlQuery
Robert
  • 2,571
  • 10
  • 63
  • 95
  • Try using `CONCAT` instead of `+`. It's way too easy to get accidental truncation with `+` if you're not religiously casting everything. – Jeroen Mostert Aug 14 '18 at 13:08
  • I have to assert that exceeding 4,000 characters with a dynamic sql statement is a pretty good indication that you need to take a hard look at what you are doing. More than likely there is a better approach to whatever it is you trying to do. – Sean Lange Aug 14 '18 at 14:15

2 Answers2

4

Your sample is wrong because you are converting after concatenation

Your concatenation is a series of short string that can't go above 4000 characters. That is. using + won't magically make the string more than 4000 characters

I described it in my answer here

This will work:

DECLARE @sqlQuery NVARCHAR(MAX);
DECLARE @sqlQueryWhere NVARCHAR(MAX);
DECLARE @sqlQueryBase NVARCHAR(MAX);
DECLARE @sqlQueryGroupyBy NVARCHAR(MAX);

SET @sqlQueryWhere = N' 1 = 1 ' --already NVARCHAR(MAX)
SET @sqlQueryBase = N'SELECT
     .... '
SET @sqlQueryGroupyBy = N' group by ... '

SET @sqlQuery = CONCAT(@sqlQueryBase, @sqlQueryWhere @sqlQueryGroupyBy)

EXECUTE sp_executesql @sqlQuery
gbn
  • 422,506
  • 82
  • 585
  • 676
0

4000 characters is 4000 characters. If I were breaking that limit, I'd look to aliasing the tables, creating views, or seeing if you can use some inline tables to help a bit.

Josh
  • 10,352
  • 12
  • 58
  • 109