Help with Function. 1

[Chuck712]

I have field in a table that contains data that looks like this:
Topic ID
heart health,joint care,bone health 12354
joint care, healthy living 21325
heart health, healthy living 25154
healthy living 56213
ect.
In order for the application that will ultimately use this information, I must be able to get each entry for topic and the ID number into a view like this:
heart health 12354
heart health 25154
joint care 21325
I created the following user defined function to accomplish this, but I am getting the following errors shown at the very bottom.
Can anyone see where I have gone wrong? (again for the 100th time today)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.client_health_topics_ind(@ind_cst_ext_key varchar(38))
RETURNS @HealthTopic Table(HealthTopic VarChar(255),ind_cst_key_ext varchar(38))
AS
BEGIN
	DECLARE @HealthDescripB TABLE(
	 HealthDescrip VarChar(600),
         ind_cst_key_ext varchar (38)
         )
		Insert Into @HealthTopic(HealthTopic,ind_cst_key_ext) 
			Select LTrim(Rtrim(ind_health_topic_of_interest_ext)),
       				ind_cst_key_ext
	From co_individual_ext
	While Exists(Select * From @HealthTopic Where CharIndex(',', HealthTopic) > 0)
  		Begin
    			Insert Into @HealthDescripB(HealthDescrip,ind_cst_key_ext)               
    Select Left(HealthTopic, CharIndex(',', HealthTopic)-1),
                ind_cst_key_ext
    From   @HealthTopic
    Where  CharIndex(',', HealthTopic) > 0
    Update @HealthTopic
    Set    HealthTopic = LTrim(RTrim(Right(HealthTopic, Len(HealthTopic)-CharIndex(',', HealthTopic))))
END 
Select * From @HealthDescripB
Insert Into @HealthDescripB(HealthDescripB,ind_cst_key_ext) 
Select Replace(HealthTopic, ',', ''),ind_cst_key_ext
From @HealthTopic
RETURN
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
------------------------------------------------------------------------------------------------------
Server: Msg 207, Level 16, State 1, Procedure client_h2u_health_topics_ind, Line 25
Invalid column name 'HealthDescripB'.
Server: Msg 444, Level 16, State 1, Procedure client_h2u_health_topics_ind, Line 24
Select statements included within a function cannot return data to a client.

 

[SQLBill]

For one thing, you create the TABLE with the column:

DECLARE @HealthDescripB TABLE(
HealthDescrip VarChar(600),


But your INSERT is:

Insert Into @HealthDescripB(HealthDescripB,

Your TABLE has the B at the end in both the DECLARE and the INSERT, but the COLUMN doesn't.

-SQLBill


Posting advice: FAQ481-4875

 

[Chuck712]

Thanks so much. You know that find-replace feature will get you sometimes. Now if I can just figure out the return part.

 

[SQLBill]

Why have this?

Select * From @HealthDescripB
Insert Into @HealthDescripB(HealthDescripB,ind_cst_key_ext) 
Select Replace(HealthTopic, ',', ''),ind_cst_key_ext
From @HealthTopic
RETURN

Your function should just do the insert. Let the calling code do the final step. Replace the above with this...

Insert Into @HealthDescripB(HealthDescripB,ind_cst_key_ext) 
Select Replace(HealthTopic, ',', ''),ind_cst_key_ext
From @HealthTopic

Then have your calling code include this:

Select * From @HealthDescripB

-SQLBill


Posting advice: FAQ481-4875

 

[Chuck712]

Thanks so much. Since this is my first attempt, I am missing quite a bit. I replaced the last line and I am not getting this error:

Server: Msg 170, Level 15, State 1, Procedure client_h2u_health_topics_ind, Line 27
Line 27: Incorrect syntax near 'END'.

Here is the complete code:

CREATE FUNCTION dbo.client_h2u_health_topics_ind(@ind_cst_ext_key varchar(38))
RETURNS @HealthTopic Table(HealthTopic VarChar(255),ind_cst_key_ext varchar(38))
AS
BEGIN
	DECLARE @HealthDescripB TABLE(
	 HealthDescrip VarChar(600),
         ind_cst_key_ext varchar (38)
         )
		Insert Into @HealthTopic(HealthTopic,ind_cst_key_ext) 
			Select LTrim(Rtrim(ind_health_topic_of_interest_ext)),
       				ind_cst_key_ext
	From co_individual_ext
	While Exists(Select * From @HealthTopic Where CharIndex(',', HealthTopic) > 0)
  		Begin
    			Insert Into @HealthDescripB(HealthDescrip,ind_cst_key_ext)               
    Select Left(HealthTopic, CharIndex(',', HealthTopic)-1),
                ind_cst_key_ext
    From   @HealthTopic
    Where  CharIndex(',', HealthTopic) > 0
    Update @HealthTopic
    Set    HealthTopic = LTrim(RTrim(Right(HealthTopic, Len(HealthTopic)-CharIndex(',', HealthTopic))))
 
Select * From @HealthDescripB
Insert Into @HealthDescripB(HealthDescrip,ind_cst_key_ext) 
Select Replace(HealthTopic, ',', ''),ind_cst_key_ext
From @HealthTopic
END

 

[Chuck712]

Ooops that should read that I AM getting the error.

 

[Chuck712]

You know, you can't do this sometimes at 6:00 in the morning. It works great! I forgot to remove the Select* from @HealthDescripB.
Have a star and I will have some coffee.

 

[Chuck712]

Now any tips to get this thing into a view?