[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Use stored procedure in a view

I got an answer on how to creaate a 'dynamic DList' routine and got the answer:
    http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24990853.html

But now I realize I need to get this result in a view?  I see a view can't have an 'exec' statement.  How do I accomplish this?
0
KCTechNet
Asked:
KCTechNet
  • 11
  • 5
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
unfortunately there is no other option as it involves the dynamic sql
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why does this need to be a view, anyhow?
0
 
KCTechNetAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
KCTechNetAuthor Commented:
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.
0
 
KCTechNetAuthor Commented:
That's why I was hoping a view would work
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
what exactly your stored procedure is doing
0
 
ralmadaCommented:
I think what you're looking for is a function. (please change the columns with the correct column names and datatypes)
and then use it like this:
select ServiceID, dbo.fn_list_str(ServiceID) from Services

CREATE FUNCTION [dbo].[fn_List_str](@Id int) RETURNS nvarchar(2000) AS 
BEGIN 
DECLARE @listStr nvarchar(1000) 
SELECT @listStr = COALESCE(@listStr+',' ,'') + Languagecolumn FROM ServiceLanguages WHERE ServiceID = @Id 
order by LanguageColumn 
 RETURN @listStr 
 END

Open in new window

0
 
KCTechNetAuthor Commented:
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.
0
 
ralmadaCommented:
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.
0
 
KCTechNetAuthor Commented:
It's hard to believe that Access can do this but SQL Server can't
0
 
ralmadaCommented:
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.
0
 
KCTechNetAuthor Commented:
Ahh, so are you saying I can put code in the ASP page to use the DList function?
0
 
ralmadaCommented:
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

Open in new window

0
 
KCTechNetAuthor Commented:
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

Open in new window

0
 
KCTechNetAuthor Commented:
Here is my DList function
0
 
KCTechNetAuthor Commented:

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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...

0
 
KCTechNetAuthor Commented:
Oh.  I was just trying ralmada's suggestion.  I thought ralmada was saying it was possible.
0
 
ralmadaCommented:
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.
0
 
KCTechNetAuthor Commented:
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...)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now