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
   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
            INSERT INTO [tbStudentPayment]
           ,'Admission Request')

Close curTemp
Deallocate curTemp

No comments:

ETG Consultancy

Web Designing Development Analysis & Promotion
Asp.Net 2.0 SQL WWF WCF SEO Marketing Ajax JQuery NHibernate MVC