This is an example of scheduling a sql job with a store procedure, also writing a cursor with in a store procedure.
alter procedure uspInitiateAdmission
As
BEGIN
declare @QueryId bigint, @StudentId bigint, @PaymentId bigint,
@RequestStatus int , @PaymentStatus int
declare curTemp cursor for
/*
fetch data based on your query and where clause
*/
SELECT t2.StudentRequestId, t2.RequestStatus, t2.RequestStatus
FROM tbStudentPayment t1 right outer JOIN tbSchoolManagementBoard t2
ON t1.QueryId = t2.StudentRequestId
where t2.RequestStatus <> 4 and
DATEADD(dd, 3, t2.RequestDateTime) >= GETDATE() and
t2.StudentRequestId not in (select QueryId from tbStudentPayment)
Open curTemp
fetch next from curTemp into @QueryId, @PaymentStatus, @RequestStatus
while @@FETCH_STATUS =0
begin
INSERT INTO [tbStudentPayment]
([QueryId]
,[StudentId]
,[PaymentId]
,[ActionDate]
,[Status])
VALUES
(@QueryId
,@StudentId
,1
,GETDATE()
,'Admission Request')
END
Close curTemp
Deallocate curTemp
END
No comments:
Post a Comment