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?

Show quoteHide quote

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

Show quoteHide quote

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

---
http://milambda.blogspot.com/

I have no blog
Trigger logic - this ok
Displaying time as 1:20 PM
xml file into table
Storing all Upper Case value
best way to bulk update
SQL 2005: Pivot question
sproc variable question-newbe
Running Average Query ? A Challenge
Database design question