CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user2171512
user2171512

Reputation: 561

EXEC sp_executesql @sql limitation

I am having problem with executing dynamic SQL Server code.

I have an insert into temp table and the number of chars is more than 4000. I tried to split into two variables and then concatenated them but problem is 

EXEC sp_executesql @sql, 
                   N'@DimStartDate int, @DimEndDate bigint',
                   @DimStartDate, @DimEndDate;

I found on net that EXEC(@sql1+@sql2) is solution but I really need input parameters. How can I solve this?

Upvotes: 1

Views: 3849

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Not sure exactly where the problem is. SQL Server will happily execute more than 4,000 characters, so your SQL must be getting truncated somewhere else. Here is an example where I've manually built up a batch that is more than 4,000 characters total so you can see that all three SELECT statements run and if you copy and paste the middle result you'll see it has the y at the end:

DECLARE @sql nvarchar(max) = N'SELECT 1;'
  + N'SELECT ''' + CONVERT(nvarchar(max), REPLICATE('x', 4096)) + N'y'';';
  + N'SELECT 2;';

EXEC sys.sp_executesql @sql;

Results:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I have had no problem with sp_executesql on strings very long strings. You simply need to declare the query to be long enough:

declare @sql nvarchar(max);

You can have an error inserting into a table if the row length is too long for the table. That would have nothing to do with the dynamic statement. The maximum length of a row is limited in SQL Server -- although you can use long strings and blobs to work around that.

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7990

I have dealt with a similar issue before, like below. Basically you need to separate your main query into pieces, then combine them in the end and use sp_EXECUTESQL to run your dynamic query.

DECLARE @SQL_Part1 varchar(4000);       
DECLARE @SQL_Part2 varchar(4000);
DECLARE @SQL_Part3 varchar(4000);
DECLARE @SQL_Part4 varchar(4000);
DECLARE @SQL_Part5 varchar(4000);
DECLARE @SQL_FullQuery nvarchar(MAX);

.. set your queries...

SET @SQL_FullQuery = CAST(@SQL_Part1 + @SQL_Part2 + @SQL_Part3 + @SQL_Part4 + @SQL_Part5 as nvarchar(MAX));
EXEC sp_EXECUTESQL @SQL_FullQuery;

Upvotes: 3

Related Questions