Calling a Stored Procedure in a stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Calling a Stored Procedure in a stored procedure?

Author

Topic

DaCheese
Starting Member

3 Posts

Posted - 2002-07-10 : 08:32:51

Here's a dummy db layout;

tblNews - NEWS_ID (int), NEWS_Name (varchar)
tblLinkNewsLocation - LINK_NewsID (int), LINK_LocationID (int)
tblLocations - LOCATION_ID (int), LOCATION_Name (varchar)

Simple stuff, each News item can have multiple locations. I'm using a stored procedure to the following query;

SELECT News_Name, LOCATION_Name FROM tblNews INNER JOIN
tblLinkNewsLocation ON tblNews.NEWS_ID = tblLinkNewsLocation.LINK_NewsID INNER JOIN
tblLocations ON tblLinkNewsLocation.LINK_LocationID = tblLocations_LocationID

This pulls out something like

NEWS_Name               Location_Name
-------------------------------------
NewsItem1               England
NewsItem1               Scotland
NewsItem1               Wales
NewsItem2               Scotland
NewsItem2               Ireland
-------------------------------------

etc.

What I want to be able to do, instead of pulling out repeats of each news item for each location, is pull out a comma-seperated string of all their locations. I.e.

NEWS_Name       Locations
-----------------------------------------
"NewsItem1"     "England, Scotland, Wales"
"NewsItem2"     "Scotland, Ireland"
-----------------------------------------

Possible? I was thinking something along the lines of calling another stored procedure, which returns all the locations for one job, and then somehow formatting the result of that stored procedure inside the main stored procedure.... but that's as far as I get :D

Any ideas on this, or a better solution to the problem, greatly appreciated.

Edited by - DaCheese on 07/10/2002 08:34:20

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 08:44:18

Try this...


declare @list varchar(8000), @lastnews varchar(30)
select @list = '', @lastnews = ''

select
    News_name,
    Location_name,
    convert(varchar(8000),'') as list
into
    #worktable
from
    tblNews
    INNER JOIN tblLinkNewsLocation
        ON tblNews.NEWS_ID = tblLinkNewsLocation.LINK_NewsID
    INNER JOIN tblLocations
        ON tblLinkNewsLocation.LINK_LocationID = tblLocations_LocationID
order by
    News_Name

update #worktable
set
    @list = list = case
                       when @lastnews <> News_Name then location_name
                       else @list + ', ' + location_name
                   end,
    @lastnews = News_Name

select
    News_name,
    max(list) as Location_name
from
    #worktable
group by 
    News_name

drop table #worktable

<O>

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-10 : 09:41:26

Or if you are on SQL 2000, you can try this:

select News_Name, dbo.NewsLocations(News_Name, ',')
from tbl
group by News_Name

CREATE FUNCTION dbo.NewsLocations(@NEWS_Name varchar(50), @Delimiter varchar(20))
RETURNS varchar(2000) AS
BEGIN
DECLARE @list varchar(2000)
SELECT @list = COALESCE(@List +@delimiter,'' ) + Location_Name
FROM tbl
WHERE NEWS_Name = @NEWS_Name
RETURN (@list)
END

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 10:39:55

Cheese, I'd be interested to know which give you better performance, as I don't have a SQL2K installation in front of me....

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources