Tags

,

Hello Everyone,

It is said that Curiosity is the Wick in the Candle of Learning. Recently I was writing a Stored Procedure where I was suppose to create related records based on each single row of the result set fetched from Query . This led me to learn about Cursors, which I’ll be sharing with you all in this post.

Cursors : It can be viewed as a pointer to one row in a set of rows. It helps to perform complex logic on a row by row basis of result set returned by database system queries.

I had the requirement to fetch the Accounts who are part of the current Scheme, and create a Payment record (1:N relationship with Accounts)for each selected Accounts.

  • Since it was suppose to run on Monthly basis hence I created a Stored Procedure and ran a scheduled Job.
  • Moreover, while creating a 1:N relationship record, I needed the Account ID and Account Name of each Selected Accounts hence was necessity of Cursor.

Working with Cursor in Stored Procedure:

1> The first thing to do is Declare a Cursor.

declare account_cursor cursor for
        select AccountId,Name from Account where new_scheme=1;
2> Then next is to Open the Cursor 

open account_cursor ;

3>  Now we Fetch data into the local variables as needed from Cursor

FETCH NEXT FROM account_cursor
    INTO @accountid,@name ;

4> Build custom business logic from the data for each row of result set.

WHILE (@@FETCH_STATUS <> -1)
BEGIN

— Insert business logic for procedure here

FETCH NEXT FROM account_cursor
    INTO @accountid,@name ;
    
END

5> Finally Close and De-allocate the Cursor when done
CLOSE account_cursor;
DEALLOCATE account_cursor;

 

I’ll also write down my final Stored Procedure which I used for my Business Logic.

**Note: This is totally an unsupported way of creating records in CRM. Due to some uncertain Circumstances I had to continue with this. So request you to kindly follow with precaution and at your own risk.

USE [Crm_MSCRM]
GO
/****** Object:  StoredProcedure [dbo].[Scheme_Payment]    Script Date: 09/15/2013 13:18:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Scheme_Payment] 
 
AS
BEGIN
    SET NOCOUNT ON;
    declare @Newid uniqueidentifier, @accountid uniqueidentifier, @name varchar(100), @scheme uniqueidentifier;
    
    set @scheme = (select top 1 new_schemeId from new_scheme where new_EndDate >= GETDATE() and GETDATE() >= new_StartDate)

    declare account_cursor cursor for 
        select AccountId,Name from Account where new_scheme=1;
        
    open account_cursor ;
    FETCH NEXT FROM account_cursor
    INTO @accountid,@name ;
    
    /* cursor loop */
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN 
    
        
    set @Newid=NEWID(); 
    
    -- Insert business logic for procedure here
insert into new_paymentBase(new_paymentId,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,OwnerId,OwningBusinessUnit,statecode,statuscode)
values (@Newid,'E76333D7-7A76-E211-B481-E41F1363AB2C',DATEadd(MINUTE,-330,GETDATE()),'E76333D7-7A76-E211-B481-E41F1363AB2C',DATEadd(MINUTE,-330,GETDATE()),'E76333D7-7A76-E211-B481-E41F1363AB2C','535A901D-18FD-E211-875E-E41F1363AB2C',0,1)

insert into new_paymentExtensionBase(new_paymentId,new_name,new_AccountIdId,new_StartDate,new_EndDate,new_SchemeId)
values(@Newid,datename(month, getdate())+' - '+datename(year, getdate())+' - '+@name,@accountid,GETDATE(),GETDATE(), @scheme)

FETCH NEXT FROM account_cursor
    INTO @accountid,@name ;
    
END

CLOSE account_cursor;
DEALLOCATE account_cursor; 

END

Hope this information about use of Cursor was Helpful.

Thanks !!!

😀

Advertisements