CODEWITHSUNDEEP
sql-serverdynamic-sqlnvarchar
Robert
Robert

Reputation: 2691

SQL Server dynamic SQL size exceed 4000 chars

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

Upvotes: 1

Views: 7309

Answers (2)

gbn
gbn

Reputation: 432271

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

Upvotes: 4

Josh
Josh

Reputation: 10604

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.

Upvotes: 0

Related Questions