I want to do something like this.

declare @x ntext
set @x=''
select @x=@x+text from syscomments where
object_name(id)= 'report_assetComponent'
select @x

When I do this, it tells me that ntext can't be a local var. The problem is,
if i change the dataType to varchar(8000) it works but it doens't give me all
the data. I need the whole sp in a variable. How would I do it?


Thank you very much for your help.

Try using a in memory table.

declare @table TABLE (text ntext)


Show quoteHide quote

It gives me an error to declare a variable named @table1. This is what I made
my query according to your suggestion.

declare @table1 table([text] ntext)
set @table1=''
insert into @table1
select text from syscomments where
object_name(id)= 'report_assetComponent'
select @table1

Please let me know what i'm doing wrong. thank you

Hi Tejas Parikh,

A table variable is treated just like a table; you aren't allowed to
directly get or set its value, you must select its column.
Just a couple changes will get your query working...
you only need to comment out line two
-- set @talbe1=''
and modify line 6
select * from @table1

Placing this in a table variable is essentially the same as having it
in a table.  You still have to select text from <table or variable> to
get it rather than being able to SET @x = @X + 'GRANT EXECUTE ON ...'
or something of the like.

If you really want your sp text in varchar type variables and sp text
is over 8000 characters, I'm afraid you're going to have to split it
into multiple varchar(8000) variables like:
select @var1 = substring(text, 1, 8000)
, @var2 = substring(text, 16000, 8000)
, etc...

I don't know if you use it, or are about to, but sql 2005 will let you
use varchar(max) to hold strings longer than 8000 characters.  Your
original query might look something like...
declare @x varchar(max)
set @x=''
select @x=@x+text from syscomments where
object_name(id)= 'report_assetComponent'
select @x

How does engine decide whether to scan or seek index
Nightmare Query
Table design (search with ranking)
Problem with DateAdd & GetDate()
Multiple-step OLE DB operation generated errors.