Creating sql Functions..Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating sql Functions..Help

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-06-02 : 09:31:42

Hello.
Please I need help with this.

I have a table named doctor_specialties where one doctor can have more than one specialty.

table: doctor_specialties
doctor_id specialty_id active
456 8952 Y
326 7895 Y
326 2569 N
326 8952 Y

I need to create a function in sql where giving two doctor_id Return true or 0 if at least one specialty(it have to be active) is the same for both doctors. or false if they don't have any specialty in comun.

Please any idea.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 10:04:41

[code]CREATE FUNCTION dbo.fnCommonSpeciality
(
@Doctor1 INT,
@Doctor2 INT
)
RETURNS BIT
AS
BEGIN
RETURN (
SELECT COUNT(*)
FROM (
SELECT Speciality_ID
FROM Doctor_Specialties
WHERE Doctor_ID IN (@Doctor1, @Doctor2)
AND Active = 'Y'
GROUP BY Speciality_ID
HAVING MIN(Doctor_ID) < MAX(DoctorID)
) AS d
)
END[/code]

N 56°04'39.26"
E 12°55'05.63"

yaditzal
Starting Member

22 Posts

Posted - 2010-06-02 : 13:26:39

Thanks for the help, but when I try to use the function I'm geeting this error.

Msg 195, Level 15, State 10, Line 1
'fnCommonNetwork' is not a recognized built-in function name.

can you tell me what is that?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-02 : 13:31:11

You have to reference it as dbo.fnCommonNetwork.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources