[Show all top banners]

virusno1
Replies to this thread:

More by virusno1
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 Cursor in T-sql
[VIEWED 4470 TIMES]
SAVE! for ease of future access.
Posted on 11-20-08 1:21 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Anybody knows in what case, we have to use Cursor? I had done Some data cleansing operation using Cursor such as Removing Duplicate Reports, Increasing the salary of employee depending upon their title. But Beside that can anybody suggest me any better and practical example which can be solved only by cursors.

 
Posted on 11-20-08 2:12 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

basically, a cursor is nothing but just a variable. but the difference is a variable can hold a value at a time but a cursor can hold multiple values at a time. So the main purpose of using cursor is to hold the entire row values so that what ever manipulation that we wanna do in the table can be done with a single command. hope that might help...........
 
Posted on 11-20-08 2:21 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Thanks Lanthus, but If we can do same operation using select or other DML statement, then use of cursor should be avoided. For example
I can store the variable using following statement also
select @prodID=Prod from Product

I am trying to find specific situation where we 'have to' use the cursor

 
Posted on 11-20-08 2:45 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Dont know abt T-sql, but can you store multiple values in @prod ?
if yes, how do u fetch it?
Generally, cursor is used to store multiple values and later fetch it sequentially.
Suppose i want to truncate some talbes then,
cursor tb is select table_name from user_talbes where table_name like "tb%"

this will store all the table names starting with tb in varable tb.
Then i fetch it to truncate one by one.
open tb
loop
fetch tb into tbname
  truncate table '|| tbname ||'
end loop
well the syntax may not be correct.



 
Posted on 11-20-08 3:43 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

hmm..thats the reasonable answer thanks techy...

 
Posted on 11-20-08 4:03 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

absoutly Techi....

don't look at how to use cursor. first of all think how you can avoid curssor .like using Temp table,Temp table variable or doing sub queries.. as you know when you use curssor, preformance become slower.... ..

ani BTW here is code for Truncate all Table in SQL 2005. might help you guys later on..

How to used:

1.- Create table to hold constraints values

2.-Populate table

3.- Create cursor to remove constraints

4.- truncate all data
 
 

Use DatabaseName --Temporary table to hold constraints info most of the time at a different location

-- or database

--This could be a temp table however set as static

IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')

truncate table T_FK_Xref

go

--Create Table to store constraint information

IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')

Create table DatabaseName.dbo.T_FK_Xref (

ID int identity (1,1),

ConstraintName varchar (255),

MasterTable varchar(255),

MasterColumn varchar(255),

ChildTable varchar(255),

ChildColumn varchar(255),

FKOrder int

)

go

--Store Constraints

insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)

SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable

,sc2.name MasterColumn

,object_name(fkeyid) ChildTable

,sc1.name ChildColumn

,cast (sf.keyno as int) FKOrder

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

go

use databaseName --Database to removed constraints

go

---Ready to remove constraints

declare @ConstraintName varchar (max) -- Name of the Constraint

declare @ChildTable varchar (max) -- Name of Child Table

declare @MasterTable varchar (max)--Name of Parent Table

declare @ChildColumn varchar (max)--Column of Child Table FK

declare @MasterColumn varchar (max)-- Parent Column PK

declare @FKOrder smallint -- Fk order

declare @sqlcmd varchar (max) --Dynamic Sql String

 

-- Create cursor to get constraint Information

declare drop_constraints cursor

fast_forward

for

SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable

,sc2.name MasterColumn

,object_name(fkeyid) ChildTable

,sc1.name ChildColumn

,cast (sf.keyno as int) FKOrder

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

open drop_constraints

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

while @@Fetch_status = 0

begin

-- Create Dynamic Sql to drop constraint

--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'

select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName

If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)

exec (@sqlcmd)

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

end

close drop_constraints

deallocate drop_constraints

go

--Removed CHECK Constraint-------------------------

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints

print 'All Constraints Disable'

go

--truncate All tables if trying to empty the database

--- Ensure the T_X_ref database is located on a different database

------------- Truncate All Tables from Model ----------------

-----To limit tables a table with sub model tables must be created and used joins-----

EXEC sp_MSForEachTable 'truncate TABLE ? '

print 'All tables truncated'

go

------------------------------------------------------------------------------------------------------

And we can find/delete duplicate record without cursor

DELETE
    FROM    
MyTable
    
WHERE    ID NOT IN
    
(
    
SELECT    MAX(ID)
        
FROM        MyTable
        
GROUP BY    DuplicatevalueColumn1DuplicateValueColumn2,
                
DuplicateValueColumn2)

 

ENJOY IN SQL WORLD/.........


 
Posted on 11-20-08 4:30 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hi,
I am looking for consultancy to learn sql server...and of coarse for marketing and placement.
I am here in STL, MO...it will be great if i can find one here....but i am flexible to move

thanks.



 
Posted on 11-20-08 5:39 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

rawbee, yeah i am absolutely agree with you that by using the cursor we have to sacrifice performance. But in some cases, instead of going into Complex T-sql query and sub-query we can use cursor.

Cursor is generally consider to be 'sauteni aama ko chora' among DBA/Developer' and everybody tries to avoid it as far as possible although it might save your life some time . But Instead of going into your complex code which you posted above. I think we can use cursor and write much simpler code by using  INFORMATION_SCHEMA system view. isn't it?

 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 90 days
Recommended Popular Threads Controvertial Threads
शीर्षक जे पनि हुन सक्छ।
NRN card pros and cons?
What are your first memories of when Nepal Television Began?
TPS Re-registration case still pending ..
Basnet or Basnyat ??
nrn citizenship
Sajha has turned into MAGATs nest
Nas and The Bokas: Coming to a Night Club near you
डीभी परेन भने खुसि हुनु होस् ! अमेरिकामाधेरै का श्रीमती अर्कैसँग पोइला गएका छन् !
3 most corrupt politicians in the world
अमेरिकामा बस्ने प्राय जस्तो नेपालीहरु सबै मध्यम बर्गीय अथवा माथि (higher than middle class)
if you are in USA illegally and ask for asylum it is legal
Travelling to Nepal - TPS AP- PASSPORT
Top 10 Anti-vaxxers Who Got Owned by COVID
emergency donation needed
निगुरो थाहा छ ??
ढ्याउ गर्दा दसैँको खसी गनाउच
Poonam pandey - death or suicide?
Doctors dying suddenly or unexpectedly since the rollout of COVID-19 vaccines
काेराेना सङ्क्रमणबाट बच्न Immunity बढाउन के के खाने ?How to increase immunity against COVID - 19?
Nas and The Bokas: Coming to a Night Club near you
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters