Custom function not working

[shaunhubbs]

Hi all,

I have a problem with a custom function that I want to be able to use for views, reports, etc. The problem is that I already have a function that does exactly what I want it to, but when I copy it and change it to specifically what I need (I only change a table name and field value that has to match) it doesn't work for me. I have been trying to edit this both in an Access ADP file and SQL Enterprise Manager -> Query Analyzer and it doesn't seem to work. I will post the code here and then describe what needs to be changed.

ALTER FUNCTION dbo.cf_slate_get_alergy (@ptt_urn int)

RETURNS varchar(100)

AS

BEGIN

declare @urn int
declare @seq int
declare @comments varchar(100)
declare @allergy varchar(50)
declare @otherallergy varchar(50)

select @comments =null

declare cr_comments cursor for
SELECT
custom_data_urn as ptt_urn,
cd_value
FROM custom_patient_data
WHERE cd_field = 434 and custom_data_urn = @ptt_urn

OPEN cr_comments

FETCH NEXT FROM cr_comments INTO @urn,@allergy

WHILE (@@fetch_status <> -1)
BEGIN
select @allergy =isnull(@allergy + '','')
if @comments = Null
begin
if @allergy <> Null
select @comments = @allergy
end
else
begin
if @allergy <> Null
select @comments = @comments + ', ' + @allergy
end
FETCH NEXT FROM cr_comments INTO @urn, @allergy
END


CLOSE cr_comments

DEALLOCATE cr_comments

RETURN @comments

END


What the above code does is open a cursor and feed in allergies from the custom_patient_data table as a patient could have more than one allegy. The entries are then retrieved from the cursor and concatenated.

Now, all I want to change is the name of the function, the cd_field value to 3095 and change the table in the select to custom_booking_data. What this would then essentially do, is grab the custom data for special equipment (it is field 3095 in the custom_booking_data table) and concatenate multiple entries as well so I can use the function to pass in a record number and get all of the pieces of special equipment back. When I tried copying and pasting the function, renaming and editing the table name and the cd_field value I got nothing and I was using a record number that I checked has multiple pieces of special equipment.

Am I missing something so simple here? Does the function somehow need to be registered and it is not doing that?

Any help would be greatly appreciated.

TIA.

- Shaun

 

[Fuzemmo]

When you change the name of the function, are you also changing the first part of your clause from "ALTER FUNCTION" to "CREATE FUNCTION"?

 

[shaunhubbs]

Yes, I did try that as well. But thanks for the thought as I did miss that first couple of times I was playing around with it.

 

[Catadmin]

Try this. Script the original UDF to a .sql file. Open it up in QA, change the name of the function and the details you need to change, then run the "Create" statement.

Then see if the function works the way you want it to.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shaunhubbs]

Thanks for the tip Catadmin. I never thought of doing it like that, but I will keep it in mind for next time I run into something like this, BUT...it was something simple that I was missing that none of you could see... I was actually on one of our many database backups on the server that did not have the same bookings as some of the others and that was the whole reason I was coming up empty. (Oops...)

Anyway, thanks again for you help guys.

- Shaun

 

[Catadmin]

Ah, yes.. I have done similar things many many times.

Like having QA open to one DB and attempting to test a Proc that is only on another DB... one of my co-workers renamed the original DB, then restored a new copy of data in the original DBs place. I knew he was doing this of course, but it completely slipped my mind that my procs wouldn't be on the new test DB because he had pulled the db from production. DOH!

At least it's Friday in my part of the world! @=)




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shaunhubbs]

Yes, TGIF for sure. I have one free Friday pass and I am using it on that one. ;o)

Thanks guys. Enjoy the rest of your Friday and have a good weekend [hopefully] away from all of this stuff. =D

- Shaun