Use stored procedure in a view

unfortunately there is no other option as it involves the dynamic sql

why does this need to be a view, anyhow?

Yeah, I started rethinking that after I posted the question.  I may just have another stored procedure that calles the DList as there are several fields that need to be lists.

Ok, I'm at a loss.  If I have a table called Services, which has a ServiceID, and other tables, such as ServicesLanguages, where a Service can have multiple languages, how would I return the list of Services with the comma seperated list.

So I would need to call the line below for every record in Services, replacing the ID number for each record:
exec dbo.usp_DList 'LanguageCodes as lc Inner Join ServicesLanguages sl on lc.LanguageCodeID = sl.LanguageCodeID Where ServiceID = 13','Language'

I can pull this off in Access, but no idea in SQL Server.  The end result is going to be an ASP page.

That's why I was hoping a view would work

what exactly your stored procedure is doing

Sorry for the confusion. I know Access, and not that polished in Stored Procedures and View.

ralmada, I already have the DList Stored Procedure created (see my original post).  I just need to know how to pull this list into the rest of the dataset.  In Access I would create a query like:

Select Services.*, Dlist('ServicesLanguages blah blah...ServicesID=' & Services.ServiceID) as LanguageList, DList('ServicesCategories blah blah...ServicesDI=' & Services.ServiceID) as CategoryList
From Services

But this is for an ASP page.

Well, you can create a function for each table that you're using following the example provided.
Unfortunately you cannot create a "general" function doing what your current stored procedure is doing because you cannot use dynamic SQL in a function.

It's hard to believe that Access can do this but SQL Server can't

The problem is that you are trying to do something that is better done at the application level and not at the database level.
Access is a mix of both Worlds.

Ahh, so are you saying I can put code in the ASP page to use the DList function?

Yes, but I cannot help you with ASP. You might want to try posting a new question in the ASP zone. From the SQL 2000 standpoint, you can try like I've mentioned, creating a function for each table you want to query (see sample code in comment http:#a26098793 attached above). and use it like this:
 

select ServiceID, dbo.fn_Language_list(ServiceID), dbo.fn_categories_list(ServiceID)
from Services

Select allOpen in new window

I created DList as a function (not stored procedure) and called it as you stated above. But I get an errror:
Only functions and extended stored procedures can be executed from within a function.

The SQL statement I am running when I get this error is attached.  The DList function is receiving the 'From/Where' part of the SQL statement and the column name to return.

Select  s.*, c.City,
 dbo.udf_Dlist('LanguageCodes lc Inner Join ServicesLanguages sl on sl.LanguageCodeID = lc.LanguageCodeID Where ServiceID = ' + convert(nvarchar(10),s.ServiceID),'Language') As LanguageList 

From Services as s
Left Join CityCodes  as c on s.CityCodeID = c.CityCodeID

Select allOpen in new window

Here is my DList function

CREATE FUNCTION [dbo].[udf_DList]
(
@SQLfrom 	varchar(2000),
@column	varchar(25)
)
RETURNS nvarchar(2000) AS  
BEGIN 
        declare @strSQL as nvarchar(2000)
        declare @params as nvarchar(2000)
        declare @x1 as nvarchar(2000) 
 
        set @strSQL = 'select @x = coalesce(@x + '','', '''') + cast(' + @column + ' as varchar)  from ' + @SQLfrom
        set @params = N'@x varchar(2000) OUTPUT' 
 
        exec sp_executeSQL @strSQL, @params, @x = @x1 OUTPUT 
 
        RETURN @x1

END

Select allOpen in new window

we can only repeat:
you CANNOT use dynamic sql inside a function. period.

and what you are trying to do cannot be done i a function, unless the possible values of @SQLFrom and  @column where limit, so you would have to create a "IF" for each possible value ...

Oh.  I was just trying ralmada's suggestion.  I thought ralmada was saying it was possible.

No. You misunderstood me. I was saying that you will have to create a function for each table. Check my comment  http:#a26098793 again for an example.

aha..worked perfectly.  I can live with that.  I would rather use one function for each item that needs to be listed, but I can create a function for each one (Language, Ethnicity, etc...)