Wednesday, May 29, 2013

Schedule Job with store procedure, store procedure with cursor

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: