Stored procedure to append fields

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure to append fields

Author

Topic

TryinToLearn
Starting Member

1 Post

Posted - 2005-09-01 : 11:07:16

Is it possible to do the following in SQL? I have a '1- many' table link with the 'many' table containing authors. I need to come up with a view (stored procedure?) that will combine any number of authors into a single view field that can be queried against, I guess a stored procedure that looks like a view for reporting purposes. The result would look something like: ITEMNMBR, Title, AuthorName to the user, a flat view of an item and all it's authors.

I figure the procedure needs to select each CaseAttributes record, selecting all authors linked to it, looping through the recordset appending to the AuthorName field.

Thanks for any help

This is more pseudo code of what I think I’m trying to do, I know my code is not correct!!!!!!!!!!!!!!!!!

CREATE PROCEDURE [dbo].[testCaseAuthorsCombined]
AS

SELECT dbo.CaseAttributes.ITEMNMBR, dbo.CaseAttributes.Title, AuthorList AS
SELECT dbo.Case_LU_Authors.AuthorLast + ', ' + dbo.Case_LU_Authors.AuthorFirst AS AuthorName
FROM dbo.Case_Authors INNER JOIN dbo.Case_LU_Authors
ON dbo.Case_Authors.AuthorID = dbo.Case_LU_Authors.AuthorID
WHERE dbo.Case_Authors.ITEMNMBR = dbo.CaseAttributes.ITEMNMBR

While not EOF
AuthorList = AuthorList +
Wend

FROM dbo.CaseAttributes LEFT OUTER JOIN
dbo.Case_Authors ON dbo.CaseAttributes.ITEMNMBR = dbo.Case_Authors.ITEMNMBR

GO

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-02 : 05:35:18

Can you give us sample input data (preferably in the form of INSERT statements) and matching expected results....?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources