Execute Dynamic SQL String larger than 4000 characters
Posted Under: Uncategorized
Although dynamic sql string is almost always prone to SQL Injection attacks but sometimes they are unavoidable. This is particularly in the case when middle tier dynamically creates where clause and send it to database (which is by the way not a good idea but it is there in the market). The problem with the dynamic SQL string is that you have the limit of nvarchar maximum length which is currently 4000 and you cannot have a string larger than 4000 characters but don’t worry, there is a workaround to overcome the maximum dynamic sql string. The workaround is to split your one big string into multiple smaller strings and execute them by using concatenation operator i.e. “+” Following example shows you the way to execute large dynamic sql string:
Use AdventureWorks
Go
DECLARE
     @q1 NVARCHAR(4000),
     @q2 NVARCHAR(4000),
     @q3 NVARCHAR(4000)
–Following is a sample dynamic sql string
–Break your dynamic string like this
SET @q1 = ‘SELECT * ‘Â Â
SET @q2 = ‘FROM HumanResources.Department ‘
SET @q3 = ‘WHERE DepartmentId=1′
–Now execute them together
EXEC (@q1 + @q2 + @q3)
Reader Comments
the above suggestion is goood enough but it cannot work with sp_executesql
Awesome thanks for this its been ahssling me for days trying to cram everthjing into one variable
Nice Work
This works with sp_executesql :
declare @request1 nvarchar(4000)
declare @request2 nvarchar(4000)
declare @request3 nvarchar(max)
– Fill @request1 with 4000 chars max
– Fill @request2 with 4000 chars max
SET @request3 = CAST (@request1 AS nvarchar(MAX)) + CAST (@request2 AS nvarchar(MAX))
EXEC sp_executesql @request3