Using User Functions In Stored Procedures

Hi There,


I've written an inline table-valued function in SQL such as the following:





ALTER FUNCTION dbo.GetCityByID( @CityID int)


RETURNS TABLE


AS


RETURN(


SELECT


Name,


Url


FROM Cities


WHERE (CityID = @CityID) )





suppose that Cities table includes three fields (CityID, Name, Url).





By the way I wrote a store procedure as follow:





ALTER PROCEDURE MyProcedure ( @MyID int)


AS


SELECT


CountryID,


OriginCityID,


DestCityID


FROM


MyTable


WHERE (MyID = @MyID)





The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name


and url of each city by its ID through this stored procedue by making relation to Cities table.


so I call GetCityByID function in my stored procedure like this:





ALTER PROCEDURE MyProcedure ( @MyID int)


AS


SELECT


CountryID,


dbo.GetCityByID(OriginCityID),


dbo.GetCityByID(DestCityID)


FROM


MyTable


WHERE (MyID = @MyID)





this procedure dosn't work an returns error.





What's your solution for getting information from Cities table for OriginCityID and DestCityID?


Thank you in advance.