How to execute dynamic query having length 8000 bytes

Question

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!!!

Bhuvanesh Mohankumar · Answer

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

Pankaj Kumar Choudhary · Answer

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.........

ali tuncer · Answer

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

How to execute dynamic query having length > 8000 bytes

Answers (3)