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