We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

while loop in storedprocedure sql server 2005

ram27
ram27 asked
on
Medium Priority
1,046 Views
Last Modified: 2012-06-27
hi

i want to write stored procedure to process each record returned from "select query".
i mean i want to use "while loop" in my stored procedure, to process each record. Could you please help
i am using sql server 2005.
Comment
Watch Question

Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
What you're describing is a cursor, but they're generally not the best way of processing data.

If you post what you need to do with each record, I'm sure there are plenty of people here who can help you avoid using a cursor (myself included).

Rob

Author

Commented:
select @StartTime=CP.StartTime from dbo.tblContact c
      inner join dbo.tblContactPeriod CP on C.ContactPeriodID = CP.ContactPeriodID
--  print @StartTime  
  -- print Datepart(hh,@StartTime)
   
-- here i need to check each @startTime which is returned in above sql select stmt
       
if((DATEPART(hh,@StartTime)>=21 and DATEPART(hh,@StartTime)<=24))
   Begin
     
          select CP.StartTime, C.ContactID, C.Product_VoicemailPhone, TMP.TextMessageEmail, C.Product_TextMessagePhone, C.Product_Email
          from dbo.tblContact C
              inner join dbo.tblContactPeriod CP on C.ContactPeriodID = CP.ContactPeriodID
             inner join dbo.tblColorDrawn CD on CD.ColorID=C.ColorID
              left outer join dbo.tblTextMessageProvider TMP on TMP.ProviderID = C.Product_TextMessageProviderID   --outer join because not everyone selects Text Messages.
         where DATEPART(hh,dbo.ConvertToGmt(CP.StartTime, C.Timezone, C.DST)) = DATEPART(hh,@GmtServer)
              and IsActive = 1
              and @CurrentTime >= StartDateService
              and CD.DateDrawn=DateADD(d,1,@CurrentTime)
             and EndDateService IS NULL
       
     
         
    End
Else
   
    Begin
   
       select CP.StartTime, C.ContactID, C.Product_VoicemailPhone, TMP.TextMessageEmail, C.Product_TextMessagePhone, C.Product_Email
       from dbo.tblContact C
              inner join dbo.tblContactPeriod CP on C.ContactPeriodID = CP.ContactPeriodID
             inner join dbo.tblColorDrawn CD on CD.ColorID=C.ColorID
              left outer join dbo.tblTextMessageProvider TMP on TMP.ProviderID = C.Product_TextMessageProviderID   --outer join because not everyone selects Text Messages.
       where DATEPART(hh,dbo.ConvertToGmt(CP.StartTime, C.Timezone, C.DST)) = DATEPART(hh,@GmtServer)
              and IsActive = 1
              and @CurrentTime >= StartDateService
              and CD.DateDrawn=@CurrentTime
              and EndDateService IS NULL
       End
     
     
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
Ok, so because the WHERE clause changes, you feel like you want to run each contactperiod separately.

So try:

and
(
DATEPART(hh,CP.StartTime)>=21 and DATEPART(hh,CP.StartTime)<=24 and CD.DateDrawn=DateADD(d,1,@CurrentTime)
or
NOT (DATEPART(hh,CP.StartTime)>=21 and DATEPART(hh,CP.StartTime)<=24) and CD.DateDrawn=@CurrentTime
)

instead of that line that changes (the "and CD.DateDrawn=@CurrentTime" line)

Rob
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
Use Cursors to process each record accordingly.
Example
http://www.mssqltips.com/tip.asp?tip=1599
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
Rahu,

Cursors are generally NOT the right answer.

Rob
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.