More than 4000 characters -- Help

[krotha]

Hi,
I am building sql query dynamically, I declared @sql nvarchar(4000). Query builds more than 4000 characters. It prints upto 'where' clause and error out with syntax.
How shall I declare more than 4000 characters. If i declare ntext or text it gives error :
Error 2739 the text,ntext,and image data types are invalid for local variables.
Thanks for any help.
Here is my procedure
CREATE procedure test
@id nvarchar(255)
as
declare @sql narchar(4000)

set @sql='select a.id, -- so many fields
from table a LEFT OUTER JOIN table b
on a.id = b.id
where id = '+@id+

Print (@sql)
Execute (@sql)
GO

 

[balves]

Can you use varchar(8000) instead?

 

[krotha]

Thanks balves for your response. I used varchar(8000) also, but my query is more characters.

I solved (block by block) like this:
CREATE procedure test
@id nvarchar(255)
as
declare @sql varchar(8000)
declare @sql1 varchar(8000)

set @sql='select a.id, -- some fields '
set @sql1='add some more fields
from table a LEFT OUTER JOIN table b
on a.id = b.id
where id = '+@id+

Execute (@sql +@sql1)
GO

atleast this is working for now.