In most programming languages one usually loops through the customer with a
do while loop

I have a list of patients with ages and sex and usually run a while loop to
do the processing.

While not eof do
end

My intent is convert this to a stored procedure.

The stored procedure takes three parameters:

SomeStoredProcedure (@PatNo, @Age, @Sex)

I would like to pass it a list of patients

Select PatNo, DateDiff(year, Birthdate, getDate() ) as Age from Patient
CASE Sex
WHEN 'M' then '2'
WHEN 'F' then '0'
END as Sex
where Birthdate is not null AND (Sex in ('M', 'F')) and PatStatus in
('A','S')


the OUTPUT of patients would be in the form of
PatNo Age Sex
1 55 2
20 50 1
30 19 1

I know I'm probably doing something wrong here but am very new to this.
The SomeStoredProc is a very long SP using #Temp Tables.

Any help appreciated.

Thanks


Stephen K. Miyasato
MDsync

Why don't you modify the SomeStoredProcedure procedure and perform your
operations on the entire set of patients instead of doing it for a single
patient at a time?

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
[quoted text, click to view]

I guess that would be the best thing to do. I ran the query and it took
about 23 seconds on a 3000 patient database
I initially created this for a single patient but done know how to
generalize it to All patients.

I have the query listed below:



CREATE PROCEDURE FLG_TempFlag_2
@PatNo int,
@Age int,
@Sex int
AS
-- Create #TempFlag Table
Set NOCOUNT ON
CREATE TABLE [#TempFlag] (
[Type] [smallint] NULL ,
[FilterType] [smallint] NULL ,
[FilterType2] [smallint] NULL ,
[ProtScr] [smallint] NULL ,
[ProtRank] [smallint] NULL ,
[Rindex] [int] NULL ,
[TypeLong] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProtocolName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Recommendations] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Freq] [int] NULL ,
[LeadTime] [smallint] NULL ,
[Repeat] [smallint] NULL ,
[ChainProc] [smallint] NULL ,
[WhenStart] [smallint] NULL ,
[AgeStart] [smallint] NULL ,
[AgeStop] [smallint] NULL ,
[Sex] [smallint] NULL ,
[Defaul_] [smallint] NULL ,
[Urgency] [smallint] NULL ,
[rowguid] [uniqueidentifier] NULL
-- [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Protoc__rowguid__28CDE297] DEFAULT (newid()),
) ON [PRIMARY]

Insert into #TempFlag
(Type, FilterType, FilterType2, ProtScr, ProtRank, Rindex,TypeLong,
Description, ProtocolName, Age, Recommendations, Freq, LeadTime,
Repeat, ChainProc, WhenStart, AgeStart, AgeStop, Sex, Defaul_, Urgency)

--Select statement of values to be inserted
select Type, FilterType, FilterType2, ProtScr, ProtRank, Rindex, TypeLong,
Description, ProtocolName, Age, Recommendations, Freq, LeadTime,
Repeat, ChainProc, WhenStart, AgeStart, AgeStop, Sex, Defaul_, Urgency from
Protoc
where FilterType in (1,2,4)
AND (AgeStart <= @Age) AND (AgeStop >= @Age)
AND (Sex IN (1,@Sex))
-- change the Disease Mananagement Flags to 0

update #TempFlag
Set Defaul_ = 0
where FilterType = 4

/*searches through PFSH database for the selected list of problems*/
update #TempFlag
Set Defaul_ = 1
where (FilterType = 4) and filterType2 in
(SELECT Distinct ProtDis.Rindex
FROM ProtDis INNER JOIN
protDisChild ON ProtDis.Rindex = protDisChild.rIndex
INNER JOIN
Pfsh ON protDisChild.ICD9 = Pfsh.ICD9
WHERE (Pfsh.PatNo = @PatNo) and PFSH.isSelected = 1)

Delete from #TempFlag
where Defaul_ <> 1

--Create Temp Table for Minimum values of ProtRank
CREATE TABLE [#TempFlag2] (
[ProtScr] [smallint] NULL ,
[ProtRank] [smallint] NULL
) ON [PRIMARY]

--Should produce Minimum value of Rank
insert into #TempFlag2
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from #TempFlag
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= @Age) AND (AgeStop >=
@Age)
AND (Sex IN (1,@Sex)) AND (Defaul_ = 1)
) AS The
GROUP BY ProtScr

-- create 3rd Table
CREATE TABLE [#TempFlag3] (
[Type] [smallint] NULL ,
[FilterType] [smallint] NULL ,
[FilterType2] [smallint] NULL ,
[ProtScr] [smallint] NULL ,
[ProtRank] [smallint] NULL ,
[Rindex] [int] NULL,
[TypeLong] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProtocolName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Recommendations] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Freq] [int] NULL ,
[LeadTime] [smallint] NULL ,
[Repeat] [smallint] NULL ,
[ChainProc] [smallint] NULL ,
[WhenStart] [smallint] NULL ,
[AgeStart] [smallint] NULL ,
[AgeStop] [smallint] NULL ,
[Sex] [smallint] NULL ,
[Defaul_] [smallint] NULL ,
[Urgency] [smallint] NULL
) ON [PRIMARY]


insert into #TempFlag3
select Type, FilterType, FilterType2, ProtScr, ProtRank, Rindex, TypeLong,
Description, ProtocolName, Age, Recommendations, Freq, LeadTime,

Repeat, ChainProc, WhenStart, AgeStart, AgeStop, Sex, Defaul_, Urgency
from #TempFlag AS F1
where EXISTS
(Select * from #TempFlag2 AS F2
WHERE F1.ProtScr = F2.ProtScr
AND F1.ProtRank = F2.ProtRank)

Insert into Flags
(PatNo, Type, Type2, Done, Description, Frequency, Repeat, Recommendations,
LeadTime, Rindex2, ChainProc, WhenStart, AgeStart, AgeStop,
TypeLong, Urgency, ProtScr)
Select @PatNo, FilterType, FilterType2, NULL as Done, Description, freq,
repeat,Recommendations,
LeadTime, Rindex, ChainProc, WhenStart, AgeStart, AgeStop,
TypeLong, Urgency, protScr
from #TempFlag3
where Rindex NOT IN (Select Rindex2 from Flags where PatNo = @PatNo and
Done is null)
--where Rindex NOT IN (Select Rindex2 from Flags where PatNo = @PatNo and
(Done <> 'Y'or Done is null))

Update Pat
SET FlagDate = getDate()
where PatNo = @PatNo

--If Patient has insurance that allows for colonscopy then change from 103
to 188 RIndex2
--Exec FLG_SigToColonoscopyIns @PatNo

SET NOCOUNT OFF


GO

[quoted text, click to view]

Have you considered using XML?

Anyway, here are a couple of articles that will help you:

Erland Sommarskog:
http://www.sommarskog.se/arrays-in-sql.html
http://www.sommarskog.se/share_data.html

Dejan Sarka:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx


ML

---