-
June 19th, 2007, 09:53 AM
#1
using Cursors within a sp
I need to use a cursor (I think) to get the next 'EventDateTime' and include it as a field called timeout. I can do this using Crystal Reports but that adds an additional step. Here is the code I use to create the procedure. Can someone point me in the right direction?
When pulling the next VisitID must = VisitID else leave field null or insert DischageDateTime
ALTER PROCEDURE [dbo].[mnmc_DiabetesMSICU]
AS
BEGIN
With AVE1(SourceID,VisitID)
as
(
SELECT DISTINCT SourceID,VisitID
FROM dbo.AdmVisitEvents
WHERE (EventDateTime > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (LocationID = 'C.MSICU')
)
Select ave.VisitID,ave.EffectiveDateTime as TimeIN, ave.LocationID, ave.RoomID, ave.Bed, ave.Code, ave.Description,
ad.DischargeDateTime, ad.DispositionID, ave.OldLocationID, av.AccountNumber, av.BirthDateTime, av.Sex, admit.AdmitDateTime
FROM AVE1 ave1 INNER JOIN
dbo.AdmVisitEvents AS ave ON ave1.SourceID = ave.SourceID AND ave1.VisitID = ave.VisitID INNER JOIN
dbo.AdmVisits AS av ON ave.SourceID = av.SourceID AND ave.VisitID = av.VisitID INNER JOIN
dbo.AdmittingData AS admit ON ave.SourceID = admit.SourceID AND ave.VisitID = admit.VisitID INNER JOIN
dbo.AdmDischarge AS ad ON ave.SourceID = ad.SourceID AND ave.VisitID = ad.VisitID
Where (ave.Code = 'ENADMIN' OR ave.Code = 'TFRADMIN') and
(NOT (ave.EventActualDateTime IS NULL)) AND
(NOT (ad.DischargeDateTime IS NULL))
ORDER BY ave.VisitID,ave.EffectiveDateTime
END
Last edited by msmouse; June 19th, 2007 at 09:57 AM.
-
June 27th, 2007, 10:15 AM
#2
Re: using Cursors within a sp
Attached is an example of a cursor being used, but from what I've read they're not always the most efficient way of working.
Code:
--Drop Procedure spRemoveAPAKDuplicates
--Go
Create Procedure spRemoveDuplicates
As
Declare @Contract Char(15)
Declare @ContractCount Int
Declare @FreeDays Int
Declare @PK Int
Declare Duplicates Cursor Fast_Forward
For
Select Contractnumber, Count(ContractNumber), Sum(FreeDays), Min(PK)
From Export
Group By ContractNumber
Having Count(ContractNumber) > 1
Open Duplicates
Fetch Next From Duplicates Into @Contract, @ContractCount, @FreeDays, @PK
While @@Fetch_Status = 0
Begin
Update Export
Set FreeDays = @FreeDays
Where PK = @PK
If @@Error <> 0
Begin
RaisError ('Problem Updating Contract %s %i',16,1, @Contract, @PK) With Log
Return
End
Delete From Export
Where PK > @PK
And ContractNumber = @Contract
If @@Error <> 0
Begin
RaisError ('Problem Deleting Contract %s %i',16,1, @Contract, @PK) With Log
Return
End
Fetch Next From Duplicates Into @Contract, @ContractCount, @FreeDays, @PK
End
Close ExportDealers
Deallocate ExportDealers
GO
Another way of doing what you want, may be to use a Do While Loop, until something is True. Set the flag to False, issue your Select statement (selecting into variables), if they match want you want then set the flag to True.
HTH
JP
Please remember to rate all postings.
-
June 28th, 2007, 08:42 AM
#3
Re: using Cursors within a sp
Thanks for the code example, I'll give it a try later.
Never thought about a Do While Loop.
That's why I was open to other suggestions. I never cared for cursors.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|