Join on a stored procedure

[CherylD]

I have a large query that I've been building into pieces. I need to select one row for each product. But a join on the itinerary for each product would return multiple rows. What I have done for the itinerary is written a stored procedure to concatenate each of the itinerarys into one item:

CREATE PROCEDURE dbo.LIST_ITINERARY (
@productid VARCHAR(255))
AS
declare @String varchar(255)
select @String = ''
select @String=@String + sysdba.LOCATION.LOCATION + ';'
FROM sysdba.PRODUCT_ITINERARY INNER JOIN
sysdba.LOCATION ON
sysdba.PRODUCT_ITINERARY.LOCATIONID =
sysdba.LOCATION.LOCATIONID
WHERE (sysdba.PRODUCT_ITINERARY.PRODUCTID =
@productid)
ORDER BY sysdba.PRODUCT_ITINERARY.ARRIVAL_DATE

create table #ItineraryTempTable
(itinerary varchar(255), productid varchar(12));

insert into #ItineraryTempTable (Itinerary, productid)
values (@String, @productid)

select Itinerary, productid from #ItineraryTempTable

drop table #ItineraryTempTable

GO

Now i want to join the results of this to another query, so that the concatenated itinerary can be selected into the line item. How do I join a stored procedure? ANd how do I pass along input to that stored procedure in that join?

 

[CherylD]

I rewrote the stored procedure to a function and I have exactly what I need. Functions are very handy.

 

[nigelrivett]

Be careful. Fuctions must be deterministic which makes them a bit inflexible.
They can also be executed for each row which can make them innefficient so you need to look at the query plan if used in a join.

Another option would be to use a temp table or table variable.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.