hi

Maximum length of dynamic sql in sql server 2008?

thanks and regards

samba ch

Hello samba,

sp_executesql accepts a nvarchar(max) datatype, so the max size can be up to 2 GB.

Hi,

There is no such limit

Declare the Sql query as VARCHAR(MAX)

eg:

DECLARE @sql AS VARCHAR(MAX)

Tthe maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

Hello Samba,

   Adding to Olaf's comment , Refer to the below thread on various ways for executing the dynamic SQL for more than 8000 length

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/45be5ec4-2038-4d06-9831-cb18207a676b

Have a look at this link

http://msdn.microsoft.com/en-us/library/ms188001.aspx

sp_executesql [ @stmt = ] statement
[ 
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
     { , [ @param1 = ] 'value1' [ ,...n ] }
]
        

Arguments

Hi there,

the variable you use to assign your Dynamic SQL string have limitations.

What is your variable data type. If you use varchar(max) or nvarchar(max) have limit of 2GB.

varchar holds Acscii and nvarchar holds non-ascscii character formats.

thanks

kumar

Length of a string containing SQL statements (batch size)1:

  65,536 * Network packet size

1Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

source: https://msdn.microsoft.com/en-us/library/ms143432%28v=sql.100%29.asp

PS: sorry for necroposting :) but this thread at top of google, so it need correct answer.

• Edited by 71rmn 3 hours 50 minutes ago