Wednesday, 15 June 2011

SQL: Fetching Record One-by-One without using Cursor

--Fetching Record One-by-One without using Cursor


DECLARE @EMP_ID CHAR( 11 )
SET ROWCOUNT 0
SELECT * INTO #MYTEMP FROM Employee

SET ROWCOUNT 1

SELECT @EMP_ID = EMP_ID FROM #Employee

WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0
SELECT * FROM #MYTEMP WHERE EMP_ID = @EMP_ID
DELETE #MYTEMP WHERE EMP_ID = @EMP_ID

SET ROWCOUNT 1
--Set one id to @EMP_ID from #MYTEMP
SELECT @EMP_ID= EMP_ID FROM #MYTEMP
END
SET ROWCOUNT 0



Remarks:

Always avoid to use cursor due to slower execution.In the above query Cursor can be
replaced by above way.I used a tempory table and Rowcount property.When ROWCOUNT is 0 ,the sql statement
can execute with any number of record operation,But when ROWCOUNT is 1
at that time the the sql statement can work with only one Record.So I have used this for my
requirement ,without using CURSOR record fetching One-By-One is possible.




Posted By:javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com