Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-07 : 08:36:06
|
Axel writes "Hi!
I ´m building a publishing service. Each Publication can belong to one or many categories.
So, now I want to write a stored procedure that returns the attributes of the publication table and as an extra attribute, the names of the categories each publication belongs to in a comma separated string:
ex:
Header = "New book released"
Text = "There has been a new release of a book!"
Categories = "books, releases, news"
I ´ve tried to make a cursor for each tuple and build a string from that but a I can ´t get it to work:
-------
Alter PROCEDURE SP_getAllPublications
AS
DECLARE @catCursor CURSOR
DECLARE @catNames varchar(500)
SELECT
publicationID,
publicationHeader,
publicationPublishDate,
publicationCreatedDate,
publicationPublish,
case when publicationId not in (SELECT publicationCategoryPublication.publicationID FROM publicationCategoryPublication WHERE categoryID > 0) then
'none' AS publicationCategoryName
else
-- bygg ihop sträng av kategorinamn. ev parametersätta skiljesträng
SET @catCursor = CURSOR FOR SELECT publicationCategoryName FROM publicationCategory,publicationCategorypublication WHERE publicationCategoryID = publicationCategoryPublication.categoryID AND publicationCategoryPublication.publicationID = publicationID
SELECT @catNames = ' '
SELECT @catNames = @catNames + FETCH NEXT FROM @catCursor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @catNames = @catNames + FETCH NEXT FROM @catCursor
SELECT @catNames = @catNames + ', '
END
CLOSE @catCursor
DEALLOCATE @catCursor
@catNames AS publicationCategoryName
end
FROM publication
-------
I want publicationCategoryName to be the comma separated string...
What is the proper way to do this??
/ Axel" |
|
|
|
|
|
building strings as return attributes