This is continuous topic of CURSOR. You can refer my past article on cursor at
http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html
http://ritesh-a-shah.blogspot.com/2009/03/forwardonly-and-fastforward-cursor.html
http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html
I gave definition of CURSOR and explained different type of CURSOR in my first article given above. Second article was for FORWARD_ONLY cursor and third one was for dynamic cross tab query with FAST_FORWARD cursor. Now this article will show you example of dynamic cursor which can move forward and backward as well. It will get fresh record set with every FETCH statement so that while fetching the record, we will be able to get fresh modified record by another user for our SELECT statement in SQL Server 2005.
Let us see it practically.
--create one table for demo
use adventureworks
CREATE TABLE emps
(
Name VARCHAR(50),
Dept VARCHAR(10),
Company VARCHAR(15)
)
--INSERT records
INSERT INTO emps
SELECT 'Ritesh','MIS','echem' UNION ALL
SELECT 'Bihag', 'MIS', 'CT' UNION ALL
SELECT 'Rajan', 'account','Marwadi' UNION ALL
SELECT 'Alka','account','tata' UNION ALL
SELECT 'Alpesh','Chemical','echem'
GO
DECLARE @Counter INT
SET @Counter =1
DECLARE @strName VARCHAR(200)
DECLARE @empCursor CURSOR
--declaring SCROLL cursor
--which will move first, last, forward and backward
SET @empCursor = CURSOR SCROLL FOR
SELECT Name FROM emps ORDER BY NAME
OPEN @empCursor
FETCH NEXT FROM @empCursor INTO @strName
--user of FETCH NEXT
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @strName + ' NEXT'
FETCH NEXT FROM @empCursor INTO @strName
SET @Counter = @Counter + 1
END
--user of FETCH PRIOR
WHILE (@Counter > 1)
BEGIN
FETCH PRIOR FROM @empCursor INTO @strName
PRINT @strName + ' PRIOR'
SET @Counter = @Counter - 1
END
--user of FETCH FIRST
BEGIN
FETCH FIRST FROM @empCursor INTO @strName
PRINT @strName +' FIRST'
END
--user of FETCH LAST
BEGIN
FETCH LAST FROM @empCursor INTO @strName
PRINT @strName +' LAST'
END
CLOSE @empCursor
DEALLOCATE @empCursor
GO
Note: this is very resource consuming cursor so think twice before use it.
Reference: Ritesh Shah/Rashmika Vaghela
0 comments:
Post a Comment