Sproc Result as Comma Seperated List

[dj shane]

Hey guys,

I had built some photoblogging software in the past and am currently just
revising it to bring it up to validated xhtml and running solely on stored
procedures.

At the beginning of the application, I initiliaze a session.list with ID's of
the photo entries. This is to control the next / previous functionality. I'm
doing this in cf right now, building a list with a loop of the result set. Is
there a way in SQL to build this comma seperated list in a SPROC, and just
bring that back to CF to manipulate.

Here's my code, but it's giving me an error.

DECLARE @EntryList varchar(255)
SET @EntryList = (SELECT Entry_ID
FROM V2_Entry
WHERE Active = 1
ORDER BY EntryDate ASC)

Error = Server: Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and
subqueries, unless TOP is also specified.

[PaulH *TMM*]

dj shane wrote:

> doing this in cf right now, building a list with a loop of the result set. Is
> there a way in SQL to build this comma seperated list in a SPROC, and just
> bring that back to CF to manipulate.

probably but wouldn't it be simpler to use valueList() on the returned resultset?

[SQLMenace]

This is how you do this kind of thing in SQl Server
--test in pubs to understand how it works

DECLARE @EntryList varchar(255)
SELECT @EntryList =''
SELECT @EntryList = @EntryList + au_fname +','
from authors where state ='UT' and state is not null
order by au_fname

select Left(@EntryList,len(@EntryList)-1)

then your would be something like this

DECLARE @EntryList varchar(255)
SELECT @EntryList = @EntryList + Entry_ID +','
FROM V2_Entry
WHERE Active = 1
ORDER BY EntryDate ASC

select Left(@EntryList,len(@EntryList)-1)