Execute Dynamic SQL String larger than 4000 characters

This post was written by namwar on October 16, 2007
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

#1 
Written By Seema on March 30th, 2008 @ 7:12 pm

Awesome thanks for this its been ahssling me for days trying to cram everthjing into one variable

#2 
Written By David on August 21st, 2009 @ 8:48 am

Nice Work

#3 
Written By Rupendra on September 1st, 2009 @ 11:04 am

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

#4 
Written By jeepee on September 6th, 2011 @ 9:17 am

Add a Comment

required, use real name
required, will not be published
optional, your blog address