UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2
This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
Reference: Pinal Dave (http://www.SQLAuthority.com), BOL
196 Comments. Leave new
Very good example.. Was of gr8 help. Thanks
Thanks, nice example
I want to know what will happen for the below scenario.
I have done some testing and found that it is a problem.
Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.
For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+ in the same table then the records inserted newly should be 4. so the total records for salary > 1000 should be 8 after the loop ends.
But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.
Please suggest how to make sure Rows selected for cursor for the first time do not change with example.
Thanks in advance
You need to post some sample data with expected result for better calrity
Very good example it is i found for a cursor..
Hi sir,
i want to know what is the difference between a procedure, function and cursor theoritically…
thanks in advance.
Have a look at SQL Server help file. If you don’t unserstand ask here
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable “@cursor”.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable “@cursor”.
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable “@cursor”.
this was the msg which i was getting at the time of execution
Plese give your syntex pramod.e
please use the ‘+@cursor+’ because u use it in the EXEC (”)
Thanks for teaching me cursor very simply…..
hi
i want to select the tables which are seeded from database creation.can any one help to create it using cursors
seed tables means the tables which have the default data when a database is created
please tell me its urgent
what ‘s the use of Insensitive keyword in cursor ?
Excellent explanation..
Dear friends,
Please Help me to solve this.
From cursor 4 Rows are returned.
But I need that 4 rows concatenated as a single row.
Thank you
Dear sir ,
Please help me ,
How i get the complete level of Binary tree ,Ex. Suppose in binary tree it complete the pair then its level is 1 , it its child complete the pair both child then parent node complete the level 2 and child node complete the level 1 same as apply for every node .
The main thing is the i create a prco in which pass the node id get the completed level of passing node
EXEC spGetLevel(1)
Ans is 2 means total pair is 3 1 for patent node and 2 pair for child node
Please Help me …..
Thanks, it’s a very usefull example
Thanks, it work perfectly for my situation.
To avoid cursors in sql server we can do it by below simple scenario..
–create temporary table with same structure of its original table
Create table #temp_table_name(id bigint primary key identity(1,1),[other parametrs that you want to include here….] )
insert into #temp_table_name( parametrs that you want to include here….)
select [parameters include here which you are going to processed]
FROM Original_table_name
–replace your cursor logic by below scenario….
SELECT @MINROWID=MIN(ID),@MAXROWID=MAX(ID) FROM #temp_table_name WITH(NOLOCK) ;
SET @CURRROWID=@MINROWID;
WHILE (@CURRROWID<=@MAXROWID)
BEGIN
——you will get one row at a time here..
SELECT @parameters1,@para2……..n
FROM #temp_table_name WHERE ID=@CURRROWID
set @CURRROWID=@CURRROWID+1
END
Hi, Would you be able to help me with the below example:
I have 3 tables with columns:
Table 1: Orders
columns: order_id; cancelled_flag;
Table 2: Orders_details
columns: order_id; product_id; quantity;
Table 3: Products
columns: product_id; amount_inventory;
If the flag in the Table 1 is set to “Y” the trigger or cursor should check what were the amounts ordered in Table 2 and add these amounts back to the appropriate products in Table 3. There is an option that there are several products ordered under one order id therefore I suppose I need a cursor.
I would be gratefull for your help.
Thank you,
can every1 help me, i want to set null when cursor row position 1, the result fetch cursor llike this :
col1 col2
A b1
A b2
i want
A b1
null b2
is there commad to check rows when position 1 . to set null?
thank you
Hi All,
Can any one help me out from this problem.
i want to copy bulk of tables not the data from one database to another database using cursor.
i had written but its throngs error.
declare c1 cursor for select name from sys.objects where type=’U’
declare @name varchar(20)
open c1
fetch next from c1 into @name
while @@FETCH_STATUS=0
begin
select * into db1.dbo.@name from db2.dbo.@name where 1=2
fetch next from c1 into @name
end
close c1
deallocate c1
Thank you this was really helpful in doing my first cursor.