How to execute dynamic query having length > 8000 bytes

Liladhar Nimje

10y2.4k

Hi,

I am executing a query using "Exec (@Query)" syntax, where @Query is having length greater than 8000 bytes in size. So when I am trying to execute this query, it is not going to execute whole query and giving error.

I have a Eg for that,

Declare

@a Varchar(8000),

@b Varchar(8000);

Select

@a = ' ', -- @a contains insert query

@b = ' '; -- @b contains where clause

Execute (@a + @b)

Thanks for your help in advance!!!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bhuvanesh Mohankumar10y ago
Hi Liladhar,
I would suggest the same what "Pankaj" mentioned in comment, you can also use "nvarchar(MAX)" instead of "VarChar(MAX)"
NVARCHAR is Unicode - 2 bytes per character, therefore max. of 1 billion characters; will handle East Asian, Arabic, Hebrew, Cyrillic etc. characters just fine.
VARCHAR is non-Unicode - 1 byte per character, max. capacity is 2 billion characters, but limited to the character set you're SQL Server is using, basically - no support for those languages mentioned before
0
Pankaj Kumar Choudhary10y ago
Hello! Liladhar i think problem is with the data type declaration.
You declared data type as varchar(8000) this declaration only allow to store maximum 8000 character but you can use varhcar(MAX) instead of this now. using varchar(max) you can store more then 2 millions characters.........
+1
ali tuncer10y ago
I think you need to cast it to varchar(max) just before you execute query.

DECLARE @sql varchar(max)
SET @sql = CONVERT(varchar(max),@a)

Check this link :
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e690c3b-967d-4e1b-99b2-94487417aaee/how-to-execute-a-long-11000-characters-dynamic-query-using-spexecutesql?forum=transactsql
0