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...........

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

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.

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

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 DuplicatevalueColumn1, DuplicateValueColumn2, DuplicateValueColumn2) ENJOY IN SQL WORLD/.........

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.

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?