Sp_executesql with Dynamic SQL string exceeding 4000

[satishchandrat]

Hi,

This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.

I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.

The following was the original approach.
===============
DECLARE @sql nvarchar(4000),
---
--
select @sql=@sql+'sele ct............. ....'
--
--
INSERT INTO #Temp
EXEC sp_executesql @SQL,@paramlist

=============== =============== =

The above sql was split into two sql variables and tried in the following way.
=============== ===============
DECLARE @sql nvarchar(4000), @sql1 nvarchar(4000), @paramlist nvarchar(4000)
---
--
select @sql=@sql+'sele ct............. ....'
select @sql1=@sql1+'fr om table1, table2......... '
--
--

INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @sql + @sql1 + ''',@paramlist' )
=============== =====

However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@sql + @sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error

Appreciate if anyone can suggest a workaround for this situation.

[iburyak]

sp_executesql does not accept concatenated strings.

I have one server that has limitation 4000 characters
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)

and another server which has 8000 characters
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

To see what you have execute sp_server_info