Returning long string in varchar using Coalesce()

Hi,
I want to return a string of words separated by comma using coalesce() so
that the client application can look for a specific word in this long
string.Following is the script:-
CREATE TABLE WordList
(
WordId int IDENTITY,
WordDesc varchar(100)
)
INSERT INTO WordList VALUES ('Lion')
INSERT INTO WordList VALUES ('Goat')
INSERT INTO WordList VALUES ('Snake')
DECLARE @.WordList varchar(8000)
SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
FROM WordList
SELECT @.WordList AS WordList
Above approach works fine.However,as the maximum size for varchar is
8000,any longer string will be truncated.I can not use "text" datatype for
this as it is not allowed.
Any workaround?Any pointers?you cannot declare a variable of text datatype. Of course in 2005 I think yo
u
can do that because its been replaced as varchar(max). Hope this helps.
"Ashish" wrote:

> Hi,
> I want to return a string of words separated by comma using coalesce() so
> that the client application can look for a specific word in this long
> string.Following is the script:-
>
> CREATE TABLE WordList
> (
> WordId int IDENTITY,
> WordDesc varchar(100)
> )
> INSERT INTO WordList VALUES ('Lion')
> INSERT INTO WordList VALUES ('Goat')
> INSERT INTO WordList VALUES ('Snake')
>
> DECLARE @.WordList varchar(8000)
> SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
> FROM WordList
> SELECT @.WordList AS WordList
>
> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?
>|||Correct.Thank you.I am aware of the same.But i am using Sql 2000 and looking
for a workaround for this.
"Omnibuzz" wrote:
> you cannot declare a variable of text datatype. Of course in 2005 I think
you
> can do that because its been replaced as varchar(max). Hope this helps.
> --
>
>
> "Ashish" wrote:
>|||Perform concatenation at the client / presentation layer?

> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?|||I want to avoid returning resultset to the presentatio layer.If i would want
i would make use of the resultset only.No concatenation would be required.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Perform concatenation at the client / presentation layer?
>
>
>
>|||SQL Server is not a text/image editor. If your content can easily be
concatenated, why is it not stored in one piece?
You could create a temporary table, and then use READTEXT/UPDATETEXT to
concatenate the values in SQL. This can hardly perform as well as a client
application.
ML
http://milambda.blogspot.com/