Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

More than 4000 characters -- Help

Status
Not open for further replies.

krotha

Programmer
Joined
Nov 5, 2000
Messages
116
Location
US
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
 
Can you use varchar(8000) instead?
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top