ram27
asked on
while loop in storedprocedure sql server 2005
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.
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.
ASKER
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)<=2 4))
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_TextMessageProvi derID --outer join because not everyone selects Text Messages.
where DATEPART(hh,dbo.ConvertToG mt(CP.Star tTime, C.Timezone, C.DST)) = DATEPART(hh,@GmtServer)
and IsActive = 1
and @CurrentTime >= StartDateService
and CD.DateDrawn=DateADD(d,1,@ CurrentTim e)
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_TextMessageProvi derID --outer join because not everyone selects Text Messages.
where DATEPART(hh,dbo.ConvertToG mt(CP.Star tTime, C.Timezone, C.DST)) = DATEPART(hh,@GmtServer)
and IsActive = 1
and @CurrentTime >= StartDateService
and CD.DateDrawn=@CurrentTime
and EndDateService IS NULL
End
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
Begin
select CP.StartTime, C.ContactID, C.Product_VoicemailPhone, TMP.TextMessageEmail, C.Product_TextMessagePhone
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
where DATEPART(hh,dbo.ConvertToG
and IsActive = 1
and @CurrentTime >= StartDateService
and CD.DateDrawn=DateADD(d,1,@
and EndDateService IS NULL
End
Else
Begin
select CP.StartTime, C.ContactID, C.Product_VoicemailPhone, TMP.TextMessageEmail, C.Product_TextMessagePhone
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
where DATEPART(hh,dbo.ConvertToG
and IsActive = 1
and @CurrentTime >= StartDateService
and CD.DateDrawn=@CurrentTime
and EndDateService IS NULL
End
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,@ CurrentTim e)
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
So try:
and
(
DATEPART(hh,CP.StartTime)>
or
NOT (DATEPART(hh,CP.StartTime)
)
instead of that line that changes (the "and CD.DateDrawn=@CurrentTime"
Rob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rahu,
Cursors are generally NOT the right answer.
Rob
Cursors are generally NOT the right answer.
Rob
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