Can you help me do this stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Can you help me do this stored procedure?

Author

Topic

waterding
Starting Member

6 Posts

Posted - 2008-06-23 : 07:47:34

I have hierrarchical table called Drug (Id, ParentId, Name)

It's data are as below. Inface, only the deepeast level stands for a drug, other levels are categories of drugs.

-- Leogs (Id=1, ParentId=NULL)
-- Gesde (Id=2, ParentId=1)
-- Esgge (Id=3, ParentId=1)

-- Anlgoy (Id=4, ParentId=NULL)
-- Tannot (Id=5, ParentId=4)
-- Oslg (Id=6, ParentId=4)
--Sllggy (Id=7, ParentId=6)
--Uopolly (Id=8, ParentId=6)

To record the relationships between Drug and HCP, I created the below tables:

HCP(Id, FirstName, LastName)
HCPDrug(Id, HCPId, DrugId)

Each HCP could have relationships with more than one drug. However, the drugId could only be the deepest level of drugs, e.g. Gesde, Esgge, Tannot, Sllggy, Uopolly.

I need a stored procedure which could return the HCP of the drug I have selected. For instance, if I select DrugId=1, it will return all HCP of Gesde and Esgge. If I select Drug=7, it will return all HCP of Sllggy. If I select DrugId=6, it will return all HCP of Oslg.

Can anyone help me with this stored procedure? I am not sure if I have created a correct database structure. If not, please give me some suggestion.

Thanks in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 08:05:21

This should give you a start:-

http://support.microsoft.com/kb/248915

waterding
Starting Member

6 Posts

Posted - 2008-06-23 : 10:35:30

quote:
Originally posted by visakh16

This should give you a start:-

http://support.microsoft.com/kb/248915

Hi,

Thanks for you reply.

I utilized the method of High Performance Hierarchies in SQL Server 2000 - Page 1 of 3 (http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm), and did the function below:

ALTER FUNCTION [dbo].[GetHCPByDrugId](@drugId int)
RETURNS @foundHCP
TABLE (Id int,
Title nvarchar(50),
FirstName nvarchar(100),
MiddleName nvarchar(100),
LastName nvarchar(100),
ContactMethod nvarchar(50),
GeneralAdvocacyOverallScore int,
GeneralAdvocacyHistory nvarchar(50),
GeneralInfluenceOverallScore int,
GeneralInfluenceHistory nvarchar(50),
HasInterviewSheet bit,
IsPublic bit,
DateTimeCreated datetime)
AS

BEGIN
DECLARE @Id int,
@Title nvarchar(50),
@FirstName nvarchar(100),
@MiddleName nvarchar(100),
@LastName nvarchar(100),
@ContactMethod nvarchar(50),
@GeneralAdvocacyOverallScore int,
@GeneralAdvocacyHistory nvarchar(50),
@GeneralInfluenceOverallScore int,
@GeneralInfluenceHistory nvarchar(50),
@HasInterviewSheet bit,
@IsPublic bit,
@DateTimeCreated datetime

DECLARE RetrieveHCP CURSOR STATIC LOCAL FOR
SELECT HCP.Id, HCP.Title, HCP.FirstName, HCP.MiddleName, HCP.LastName,
HCP.ContactMethod, HCP.GeneralAdvocacyOverallScore, HCP.GeneralAdvocacyHistory,
HCP.GeneralInfluenceOverallScore, HCP.GeneralInfluenceHistory,
HCP.HasInterviewSheet, HCP.IsPublic, HCP.DateTimeCreated
FROM HCP, HCPDrug, Drug
WHERE HCP.Id = HCPDrug.HCPId AND HCPDrug.DrugId = Drug.Id AND Drug.Id=@drugId

OPEN RetrieveHCP

FETCH NEXT FROM RetrieveHCP
INTO @Id,
@Title,
@FirstName,
@MiddleName,
@LastName,
@ContactMethod,
@GeneralAdvocacyOverallScore,
@GeneralAdvocacyHistory,
@GeneralInfluenceOverallScore,
@GeneralInfluenceHistory,
@HasInterviewSheet,
@IsPublic,
@DateTimeCreated

WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @foundHCP
SELECT * FROM dbo.GetHCPByDrugId(@Id)

INSERT INTO @foundHCP
VALUES(@Id,
@Title,
@FirstName,
@MiddleName,
@LastName,
@ContactMethod,
@GeneralAdvocacyOverallScore,
@GeneralAdvocacyHistory,
@GeneralInfluenceOverallScore,
@GeneralInfluenceHistory,
@HasInterviewSheet,
@IsPublic,
@DateTimeCreated)

FETCH NEXT FROM RetrieveHCP
INTO @Id,
@Title,
@FirstName,
@MiddleName,
@LastName,
@ContactMethod,
@GeneralAdvocacyOverallScore,
@GeneralAdvocacyHistory,
@GeneralInfluenceOverallScore,
@GeneralInfluenceHistory,
@HasInterviewSheet,
@IsPublic,
@DateTimeCreated
END

CLOSE RetrieveHCP
DEALLOCATE RetrieveHCP

RETURN
END

And then run the stored procedure

SELECT * FROM dbo.GetHCPByDrugId(2)

If I set the DrugId to be the deepest levels, e.g. 2, 3, 5, 7, 8, it works ok.

If I set the DrugId to be upper levels e.g. 1, 4, 6, it returns nothing.

Any idea?

PS:
Each HCP could have relationships with more than one drug. However, the drugId could only be the deepest level of drugs, e.g. Gesde, Esgge, Tannot, Sllggy, Uopolly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 10:37:57

Is this sample data correct?

-- Leogs (Id=1, ParentId=NULL)
-- Gesde (Id=2, ParentId=2)
-- Esgge (Id=3, ParentId=2)

E 12°55'05.25"
N 56°04'39.16"

waterding
Starting Member

6 Posts

Posted - 2008-06-23 : 10:43:47

quote:
Originally posted by Peso

Is this sample data correct?

-- Leogs (Id=1, ParentId=NULL)
-- Gesde (Id=2, ParentId=2)
-- Esgge (Id=3, ParentId=2)

E 12°55'05.25"
N 56°04'39.16"

Ah, it should be

-- Leogs (Id=1, ParentId=NULL)
-- Gesde (Id=2, ParentId=1)
-- Esgge (Id=3, ParentId=2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 11:32:41

[code]CREATE FUNCTION dbo.GetHCPByDrugID
(
@DrugID INT
)
RETURNS @Result TABLE (ID INT)
AS
BEGIN
DECLARE @Seq INT

SET @Seq = 0

DECLARE @Map TABLE
(
Seq INT,
ID INT
)

INSERT @Map
SELECT 0,
ID
FROM TABLE1
WHERE ID = @DrugID

WHILE @@ROWCOUNT > 0
BEGIN
SET @Seq = @Seq + 1

INSERT @Map
(
Seq,
ID
)
SELECT @Seq,
s.ID
FROM TABLE1 AS s
INNER JOIN @Map AS m ON m.ID = s.ParentID
LEFT JOIN @Map AS x ON x.ID = s.ID
WHERE x.ID IS NULL
AND m.Seq = @Seq - 1
END

INSERT @Result
SELECT m.ID
FROM @Map AS m
LEFT JOIN TABLE1 AS s ON s.ParentID = m.ID
WHERE s.ID IS NULL

RETURN
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources