setting a variable to = multiple rows

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

SQL Server Development (2000)

setting a variable to = multiple rows

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-12-22 : 19:56:57

Hi there,

I have 2 tables, one with company info the other with keywords relating to that company. I have a stored proc that returns the info from the first company table. In this stored proc, I would like to include in the select statement all the keywords from the second table (each keyword separated by a comma) so I can display all keywords back into one text box for editing on the front end.

This is obviously wrong but may give you a better idea of what Im trying to achieve:

Select CompanyName, CompanyDescription, (Select keywords from tblCompanyKeywords Where Companyid = @COMPANYID) As CompanyKeywords
From tblCompany Where CompanyID = @COMPANYID

Is possible to do something similar or do I have to use JOIN?

thanks - matt

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-22 : 20:07:00

Hi Matt

You need an IN condition. Take a look at these : http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Damian

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-12-22 : 20:33:08

Hi Merkin,

thanks for your reply.

I ended up taking info from this article:
http://www.sqlteam.com/item.asp?ItemID=2368

Here is my complete stored proc - let me know if you think this is ok:

CREATE PROCEDURE p_SELECT_MEMBERDOCS_MEMBERDOCID

@MEMBERDOCID int

AS

DECLARE @KEYWORDS AS VARCHAR(4000)

SELECT @KEYWORDS = COALESCE(@KEYWORDS + ', ', '') + keyword
FROM tblMemberDocs_Keywords
WHERE MEMBERDOCID = @MEMBERDOCID

SELECT D.MEMBERDOCID, D.MEMBERID, D.PARENTID, D.URL, D.DESCRIPTION, D.CHARTERED_EMPLOYEES,
D.MEMBER1, D.MEMBER2, D.MEMBER3, D.MEMBER4, D.MEMBER5, D.COMPANYSIZE, D.IMAGE1, D.VALIDATED, D.STATUS, D.VERSIONNO,
D.DATECREATED, D.DATEUPDATED, D.ADMINID,
(SELECT @KEYWORDS) AS KEYWORDS,
M.BUSINESSNAME,
A.FLDUSERNAME
FROM tblMemberDocs D
INNER JOIN tblMembers M on M.MEMBERID = D.MemberID
LEFT OUTER JOIN tblAdministrators A on A.ADMINID = D.ADMINID
WHERE D.MEMBERDOCID = @MEMBERDOCID

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-22 : 22:18:49

You can also do.

SELECT @KEYWORDS = @KEYWORDS + ', ' + keyword
FROM tblMemberDocs_Keywords
WHERE MEMBERDOCID = @MEMBERDOCID

SELECT @KEYWORDS = SUBSTRING(@KEYWORDS,3,DATALENGTH(@sqlvar)/2)

You don't need select for @keywords

SELECT D.MEMBERDOCID, D.MEMBERID, D.PARENTID, D.URL, D.DESCRIPTION,
D.CHARTERED_EMPLOYEES, D.MEMBER1, D.MEMBER2, D.MEMBER3, D.MEMBER4,
D.MEMBER5, D.COMPANYSIZE, D.IMAGE1, D.VALIDATED, D.STATUS,
D.VERSIONNO, D.DATECREATED, D.DATEUPDATED, D.ADMINID,
@KEYWORDS AS KEYWORDS, M.BUSINESSNAME, A.FLDUSERNAME
FROM tblMemberDocs D
INNER JOIN tblMembers M on M.MEMBERID = D.MemberID
LEFT OUTER JOIN tblAdministrators A on A.ADMINID = D.ADMINID
WHERE D.MEMBERDOCID = @MEMBERDOCID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources