how and when to use cursors in sqlserver

Check the following

http://en.wikipedia.org/wiki/Cursor_(databases)

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

use cursor.

when u can fetch one bye one record into database that time cursor in use full or u can add one one record inset into another table that time use cursor


thanks
sagar

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

example of cursor

insert record one table to another

et ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE [dbo].[proc_insertuserprofilecur]

@ImportProcessId uniqueidentifier

as

DECLARE @STREETADDRESS nvarchar(256),@MOBILE VARCHAR(50),@MAIL nvarchar(256),@DEPARTMENT nvarchar(256),@SN nvarchar(256),@GIVENNAME nvarchar(256),@SAMACCOUNTNAME nvarchar(250)


DECLARE CUR_INSERT_DATA CURSOR FOR


--SELECT *
------FROM OPENQUERY (ADSI,'SELECT STREETADDRESS,MOBILE,MAIL,DEPARTMENT,SN,GIVENNAME,SAMACCOUNTNAME,OBJECTGUID FROM ''LDAP://DC=EMANON,DC=COM'' WHERE OBJECTCLASS=''USER''')
-- FROM OPENQUERY (ADSI,'SELECT SAMACCOUNTNAME,GIVENNAME,SN,DEPARTMENT, MAIL,MOBILE,STREETADDRESS FROM ''LDAP://DC=EMANON,DC=COM'' WHERE OBJECTCLASS=''USER''')
--
----EXEC (@adsiquery)
select * from Ldaptable
BEGIN

OPEN CUR_INSERT_DATA

FETCH NEXT FROM CUR_INSERT_DATA

INTO

--@STREETADDRESS,@MOBILE,@MAIL,@DEPARTMENT,@SN,@GIVENNAME,@SAMACCOUNTNAME,@OBJECTGUID
@SAMACCOUNTNAME,@GIVENNAME,@SN,@DEPARTMENT,@MAIL,@MOBILE,@STREETADDRESS

WHILE @@FETCH_STATUS=0

BEGIN


INSERT INTO USER_PROFILES

(ACCOUNTNAME,FIRSTNAME,LASTNAME,DEPARTMENT,EMAIL,MOBILE,ADDRESS,ImportProcessId)

VALUES

--(@SAMACCOUNTNAME,@GIVENNAME,@SN,@DEPARTMENT,@MAIL,@MOBILE,@STREETADDRESS)
(@STREETADDRESS,@MOBILE,@MAIL,@DEPARTMENT,@SN,@GIVENNAME,@SAMACCOUNTNAME,@ImportProcessId)



FETCH NEXT FROM CUR_INSERT_DATA

INTO

--@STREETADDRESS,@MOBILE,@MAIL,@DEPARTMENT,@SN,@GIVENNAME,@SAMACCOUNTNAME,@OBJECTGUID
--@STREETADDRESS,@MOBILE,@MAIL--,@DEPARTMENT,@SN,@GIVENNAME,@SAMACCOUNTNAME,@OBJECTGUID
@SAMACCOUNTNAME,@GIVENNAME,@SN,@DEPARTMENT,@MAIL,@MOBILE,@STREETADDRESS

END

CLOSE CUR_INSERT_DATA

DEALLOCATE CUR_INSERT_DATA

END

Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Since cursor occupies huge number of memmory resources, it's not adviced to use cursor always. You can try for alternatives of cursor.

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Its is not good practice to use cursor.
Because cursor occupies Heavy Memory load in SQL Server.

You can you WHILE loop instead of it.

Create temp table in procedure with Identity column

Assign records in that table

And iterate it with while loop filter by the identity field and While loop variable

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator