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