Friday, March 23, 2012

EXEC / Sp_executesql with Dynamic SQL string exceeding 4000 - SQL Server 2000


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+'select.................'
--
--
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+'select.................'
select @.sql1=@.sql1+'from 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.

Try the other version of executing dynamic SQL the simple one....

EXEC(@.sql + @.Sql1)

Because i have used this 2 one place where my Dynamic SQl even exceeds 1lakh characters....|||

Thanks Mandip. It's working. Appreciate your help.

No comments:

Post a Comment