CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    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.

  2. #2
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    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.

  3. #3
    Join Date
    Jun 2007
    Posts
    4

    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
  •  





Click Here to Expand Forum to Full Width

Featured