You must Sign In to post a response.
  • Category: SQL Server

    What is meant by cursors?where we need?

    Hi

    what is meant by cursors?where we need?

    advantages and disadvantages with examples

    thanks
  • #572091
    A cursor is a database object that helps in accessing and manipulating data in a given result set.The main advantage of cursors is that you can process data row-by-row.

    A result set is defined as a collection of rows obtained from a SELECT statement that meet the criteria specified in te WHERE clause.

    Cursors,therefore,serve as a mechanism for applications to operate on a single row or a set of rows.Cursors enable the processing of rows in the given result set in the following ways:

    1>Allow specific rows to be retrieved from the result set.
    2>Allow the current row in the result set to be modified.
    3>Help navigate from the current row in the result set to a different row.
    4>Allow data modified by other users to be visible in the result set.

    Structure of Cursors:
    The following tasks need to be performed while using a cursor in SQL Server:

    1>The cursor needs to be defined and its attributes need to be set.
    2>The cursor needs to be opened.
    3>The required rows need to be fetched from the cursor.Fetch refers to the process of retrieving a row from the result set.
    4>The data in the current row of the cursor can be modified,if required.
    5>The cursor needs to be closed.
    6>The cursor should be de-allocated.This is a good practice as resources used by the cursor are released.


    Thanks & Regards


    Bunty

  • #572093
    Cursor are used to perform a repeted operation in data base Row you can say a Loop here is a sample


    ALTER procedure [dbo].[menuitems]

    as


    begin
    SET NOCOUNT ON;

    create table #temptdc_itms ([role_Desc] varchar(1000), home int, billings int,
    Reports int, projects int, [user] int, [Role] int,IHome int,ISheet int, tractInfo int)
    declare @role_type int
    declare @role_description varchar(1000)
    declare test2 cursor for

    select pk_role_id, role_description from l_role




    open test2


    fetch next from test2 into @role_type, @role_description

    while @@fetch_status=0

    begin


    insert into #temptdc_itms ([Role_Desc], Home,Billings,Reports,Projects,[User],[Role], IHome, ISheet,tractInfo)
    select @role_description, 'Home' =(select status from l_menu_permissions where role_id= @role_type and menu_id=1 and status = 1),
    'Billings' =(select status from l_menu_permissions where role_id= @role_type and menu_id=2 and status = 1),
    'Reports' =(select status from l_menu_permissions where role_id= @role_type and menu_id=3 and status = 1),
    'Projects' =(select status from l_menu_permissions where role_id= @role_type and menu_id=4 and status = 1),
    'User' =(select status from l_menu_permissions where role_id= @role_type and menu_id=5 and status = 1),
    'Role' =(select status from l_menu_permissions where role_id= @role_type and menu_id=6 and status = 1),
    'IHome' =(select status from l_menu_permissions where role_id= @role_type and menu_id=7 and status = 1),
    'Isheet' =(select status from l_menu_permissions where role_id= @role_type and menu_id=8 and status = 1),
    'tractInfo' = (select status from l_menu_permissions where role_id= @role_type and menu_id=9 and status = 1)

    fetch next from test2 into @role_type, @role_description
    end

    close test2


    deallocate test2

    select * from #temptdc_itms
    --drop table #temptdc_itms

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #573390
    Hi,

    Refer the below site links...

    http://www.mssqltips.com/tip.asp?tip=1599

    http://www.mssqlcity.com/Articles/General/UseCursor.htm

    http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/


    http://msdn.microsoft.com/en-us/library/ms180169.aspx

    Thanks & Regards
    Usha R


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.