Create a view that uses results from a store procedure

[Chuck712]

I have a stored procedure that "de-concatenates" a text column that was poorly designed. The column has results from a survey where the responses are:
Healthy lifestyles
Healthy lifestyles,Mens health
Healthy lifestyles,familiy health,cancer prevention
Family health, bone and joint care.......
ect.
The procedure works fine, but the application won't allow me to use a sproc as a list table to allow the users to query the information on the front end. It requires a view.
From everything I can find, you can't call a sproc from a view. Is that correct? Or am I missing something.
The vendor says you can, but so far I haven't been able to make it work. I am fairly new to TSQL, so this could be my inexperience.

 

[AlexCuse]

If possible, you might want to consider changing your stored proc to accept parameters, so that users can 'query' the db without necessarily 'querying' it...

If you post your stored procedure, I am sure that we will be able to help you get it into a view (you might need to use a series of views though)

What is the front end?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Chuck712]

Thanks. Here is the SP that I had planned to be used. I have another that runs with variables, since I was going to just put this into a report. The application is a .net, with SQL back end. Inside the application is a way to use "list tables" and have the end user run GUI type query's and create mailing labels and email list with out having to export a file.
Here is the code that I wanted to use for the view.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_h2u_health_topics_individual_affiliate

AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Create Table #h2uHealthTopicIND (HealthTopic varchar(600),
chp_code nvarchar(10),
chp_name nvarchar(150),
cst_name_cp nvarchar (150),
cst_eml_address_dn nvarchar(80),
cxa_mailing_label nvarchar (450),
chp_cst_key varchar (38),
ind_cst_key varchar(38))



Create Table #h2uHealthDescripIND (HealthDescrip varchar(255),
chp_code nvarchar(10),
chp_name nvarchar(150),
cst_name_cp nvarchar (150),
cst_eml_address_dn nvarchar(80),
cxa_mailing_label nvarchar (450),
chp_cst_key varchar (38),
ind_cst_key varchar(38))

Insert Into #h2uHealthTopicIND
Select LTrim(Rtrim(ind_health_topic_of_interest_ext)),
chp_code ,
chp_name ,
cst_name_cp ,
cst_eml_address_dn ,
cxa_mailing_label ,
chp_cst_key,
ind_cst_key
From co_individual_ext
JOIN co_individual ON ind_cst_key = ind_cst_key_ext
LEFT JOIN co_customer ON cst_key=ind_cst_key
Left Join mb_membership (nolock)ON mbr_cst_key=cst_key
Left Join co_chapter (NOLOCK) ON chp_cst_key=mbr_chp_cst_key
LEFT JOIN mb_association (NOLOCK) ON asn_key=chp_asn_key
JOIN co_customer_x_address (Nolock)ON cst_cxa_key = cxa_key
LEFT JOIN co_address (NOLOCK) ON adr_key=cxa_adr_key
WHERE ind_health_topic_of_interest_ext IS NOT NULL AND chp_code IS NOT NULL



While Exists(Select * From #h2uHealthTopicIND Where CharIndex(',', HealthTopic) > 0)
Begin
Insert Into #h2uHealthDescripIND(HealthDescrip,chp_code,chp_name,cst_name_cp,cst_eml_address_dn,cxa_mailing_label,chp_cst_key,ind_cst_key)
Select Left(HealthTopic, CharIndex(',', HealthTopic)-1),
chp_code ,
chp_name ,
cst_name_cp ,
cst_eml_address_dn ,
cxa_mailing_label ,
chp_cst_key,
ind_cst_key
From #h2uHealthTopicIND
Where CharIndex(',', HealthTopic) > 0

Update #h2uHealthTopicIND
Set HealthTopic = LTrim(RTrim(Right(HealthTopic, Len(HealthTopic)-CharIndex(',', HealthTopic))))
End

Insert Into #h2uHealthDescripIND(HealthDescrip,chp_code,chp_name,cst_name_cp,cst_eml_address_dn,cxa_mailing_label,chp_cst_key,ind_cst_key)
Select REPLACE(HealthTopic,',',''),chp_code,chp_name,cst_name_cp,cst_eml_address_dn,cxa_mailing_label,
chp_cst_key,ind_cst_key


From #h2uHealthTopicIND
Select s03_health_topic = HealthDescrip,
s03_chp_code = chp_code,
s03_chapter=chp_name,
s03_name =cst_name_cp,
s03_email =cst_eml_address_dn,
s03_mailing_label = cxa_mailing_label,
s03_cst_key = ind_cst_key


From #h2uHealthDescripIND

DROP TABLE #h2uHealthTopicIND
DROP TABLE #h2uHealthDescripIND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[AlexCuse]

Chuck - you should try to use [ignore]

typing code

[/ignore] tags when posting code. This will give the result

typing code

which will make it easier to read.

I don't have a ton of time to read that right now, but I would say your best bet is to create a view that displays the same result of your stored procedure. You can nest views (build 'intermediate' views that help you get to the desired result). I'm not sure how deep you can go with these, but I think you will be able to get it done in just one or two.

Your inserts to temp tables could be converted to select statements that will be used in views.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Chuck712]

Thanks so much. Yeah, I always forget to paste the code into those tags. I have contacted the vendor as well. I really appreciate your help.

 

[AlexCuse]

No problem. Post back if you have any problem creating the view you need.

ALex

Ignorance of certain subjects is a great part of wisdom

 

[Chuck712]

Okay. I should change the heading of this thread, but here is where I am. I created a view to start this.
Here is the code:

ALTER       VIEW dbo.vw_h2u_health_topics_list
AS 


Select v11_health_topic =LTrim(Rtrim(ind_health_topic_of_interest_ext)),
       v11_cst_key=ind_cst_key_ext

From co_individual_ext

Where  ind_health_topic_of_interest_ext Is NOT Null

I get results like this

Heart & vascular health,Women's health,Caregiving	1EA5CC74
Heart & vascular health,Health screenings,Cancer detection	2A24598A
Heart & vascular health,Women's health,Health screenings	2B53564F
Women's health,Health screenings	3D42E7A7
Heart & vascular health,Women's health,Caregiving	4A0873085
Heart & vascular health,Women's health,Bone & joint care	6A152971
Heart & vascular health,Immunizations,Healthy lifestyles	6CC4751E
Heart & vascular health,Health screenings,Health seminars	8D5C01D4
Women's health,Health screenings,Bone & joint care,	BBDF7462
Heart & vascular health,Health screenings,Men's health	CA333596
Heart & vascular health,Bone & joint care,Men's health	D14F8A36

I need to get this:
Heart & vascular health 1EA5CC74
Womens Health 1EA5CC74
Caregiving 1EA5CC74
Heart & vascular health 2A24598A
Health screenings 2A24598A
Cancer detection 2A24598A

ect.
One topic to many cst_key's
Any tips on nesting a view?
Or just getting a view to return these results.

 

[SQLSister]

how do you know which record you want? YOu can use group by and min or max to select just one.

Questions about posting. See faq183-874

 

[Chuck712]

Thanks. The record (health topic) will be chosen at run time from the application. For instance, they will chose heart & vascular health. Then (after the rest of the tables are joined) I need a list of only those who chose that topic.

 

[SQLSister]

maybe this can give you some ideas

http://www.tek-tips.com/faqs.cfm?fid=1151

Questions about posting. See faq183-874

 

[Chuck712]

Thanks. I am looking at a user defined function to convert this SP and then call the results from the function into a view, which should solve the issue. Any help or direction would be appreciated, since I have never done this before.