Cursor Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor Issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 17:36:41

I have a tbl_emp table with 1000 records and I need the cursor to handle only 100 records and pass each emp_number one by one to the usp_emp_calc @emp_number stored procedure and once the 100 records are finishe I need to pass the next 100 records :

Create table tbl_emp
(
emp_number varchar(13)NOT NULL,
status varchar(20) not null,
process_start_date datetime NULL
process_end_date datetime NULL
)

Initially the status will be new and as soon as one emp_number is processed it changes to 'COMPLETE'

My problem is how to pass only 100 records each and complete the process.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 17:41:51

Is it possible ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-25 : 17:51:22

Use SET ROWCOUNT to control the number of rows to process.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 18:12:27

If I use SET ROWCOUNT : First time I will get 100 records and then I pass each of these emp_numbers to the Sp then how would I get the next 100 records.Should I use 2 cursors then

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-25 : 18:18:59

Please post DDL for your tables, DML for sample data, and the expected result set using the sample data.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 18:34:44

Create table tbl_emp
(
emp_number varchar(13)NOT NULL,
status varchar(20) not null,
process_start_date datetime NULL
process_end_date datetime NULL
)

INSERT INTO tbl_emp
(emp_number,status,process_start_date)

SELECT 007576,'NEW',getdate() UNION
SELECT 007456,'NEW',getdate() UNION
SELECT 007376,'NEW',getdate() UNION
SELECT 006586,'NEW',getdate() UNION
SELECT 007356,'NEW',getdate() UNION

SELECT 007346,'NEW',getdate() UNION
SELECT 007896,'NEW',getdate() UNION
SELECT 007589,'NEW',getdate() UNION
SELECT 003467,'NEW',getdate() UNION
SELECT 005892,'NEW',getdate() UNION

SELECT 023456,'NEW',getdate() UNION
SELECT 023576,'NEW',getdate() UNION
SELECT 012376,'NEW',getdate() UNION
SELECT 344676,'NEW',getdate() UNION
SELECT 005692,'NEW',getdate() UNION

SELECT 034576,'NEW',getdate() UNION
SELECT 018246,'NEW',getdate() UNION
SELECT 007878,'NEW',getdate() UNION
SELECT 007776,'NEW',getdate() UNION
SELECT 008886,'NEW',getdate()

This is the cursor Iam currently using

DECLARE active_emp_cur CURSOR READ_ONLY
FOR
SELECT emp_number FROM tbl_emp WHERE status='NEW'

OPEN active_emp_cur
FETCH NEXT FROM active_emp_cur INTO @emp_number

SET @loop_restriction_id=0
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @loop_restriction_id
EXECUTE usp_emp_calc @emp_number

PRINT 'emp_number' + '-' + @emp_number + 'processed'
FETCH NEXT FROM active_emp_cur INTO @emp_number
SET @loop_restriction_id=@loop_restriction_id+1
PRINT 'No of emps processed - ' + CONVERT(VARCHAR,@loop_restriction_id)
END

CLOSE active_emp_cur
DEALLOCATE active_emp_cur

Now I need the "SELECT emp_number FROM tbl_emp WHERE status='NEW' " to give me only 5 records at time and
I want to process those 5 one by one through the stored procedure and then move to next 5 records.
Continue till I finish my records in tbl_emp with status='NEW'

Note :- I commit for each emp_number inside the stored procedure.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-25 : 18:39:58

If you have to process all the rows anyway, why run them 5 at a time (or 100)?

I know better than to ask whether usp_emp_calc can be written to work on more than one emp_number at a time...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-25 : 18:41:16

Here's one way:


Create table tbl_emp
(
emp_number varchar(13)NOT NULL,
status varchar(20) not null,
process_start_date datetime NULL,
process_end_date datetime NULL
)

INSERT INTO tbl_emp
(emp_number,status,process_start_date)

SELECT 007576,'NEW',getdate() UNION
SELECT 007456,'NEW',getdate() UNION
SELECT 007376,'NEW',getdate() UNION
SELECT 006586,'NEW',getdate() UNION
SELECT 007356,'NEW',getdate() UNION

SELECT 007346,'NEW',getdate() UNION
SELECT 007896,'NEW',getdate() UNION
SELECT 007589,'NEW',getdate() UNION
SELECT 003467,'NEW',getdate() UNION
SELECT 005892,'NEW',getdate() UNION

SELECT 023456,'NEW',getdate() UNION
SELECT 023576,'NEW',getdate() UNION
SELECT 012376,'NEW',getdate() UNION
SELECT 344676,'NEW',getdate() UNION
SELECT 005692,'NEW',getdate() UNION

SELECT 034576,'NEW',getdate() UNION
SELECT 018246,'NEW',getdate() UNION
SELECT 007878,'NEW',getdate() UNION
SELECT 007776,'NEW',getdate() UNION
SELECT 008886,'NEW',getdate() 

DECLARE @i int

SELECT IDENTITY(int, 1, 1) AS empId, emp_number, status, process_start_date
INTO #Temp
FROM tbl_emp

SET @i = @@ROWCOUNT

SET ROWCOUNT 5

WHILE @i <= 0
BEGIN
	--Do Something...
	
	SELECT @i = @i - 5
END

DROP TABLE tbl_emp, #Temp

SET ROWCOUNT 0

Just change Do something to what you want to do with the 5 rows.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 18:56:15

Since its not 20 records its millions of records in the table.I want to approach it in a batch by batch approach and within the batch do one by one..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-25 : 18:57:57

That'll take forever if there's millions of rows! We do our batches in 10000 row chunks.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 19:03:10

IF I use 10000 row chunks then should I use the while loop or the cursor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-25 : 19:10:02

It doesn't matter. I prefer WHILE loops as it gives you complete control.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 20:36:36

Iam really sorry..Its not working the way I want.I have explained it in the code.When I run the query Iam not getting the results I want to see..

DECLARE @i int
DECLARE @emp_number varchar(13)
DECLARE @j INT
SET @j=1

-- I have added the emp_inc_id as identity_insert column
SELECT emp_inc_id,emp_number from tbl_emp where status='NEW'

--- Here I will get all the records in the table with NEW - lets say 10000 records Now I need
-- to process in 500 bulks .

SET @i = @@ROWCOUNT
PRINT @i

SET ROWCOUNT 500
-- Now I can set the rowcount to 500

WHILE @i <= 0
BEGIN
SELECT @emp_number = emp_number from tbl_emp
-- Now I need to pass these 500 emp_number to the below shown stored procedure one by one
EXECUTE usp_emp_calc @emp_number -- (Here it will commit emp_number by emp_number)
SELECT @j=@j+1
SELECT @i = @i - 2
-- Now when these 500 is over it should take the next 500 emp_number from the tbl_emp table
END

SET ROWCOUNT 0

-- It will finish processing the complete 1000 records in 500 each packets though its one by one

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 23:02:29

How can I use the while loop as I have mentioned above

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-26 : 11:36:17

There is no point to doing 500 or 10000 at a time if you are passing in one value to a stored procedure. Absolutely no point. Your stored procedure is committing the data after each emp_number anyway.

I don't understand the point of what you are trying to do, hence not being able to help much.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 11:38:48

First you said 1000 records. Now you say millions of records...

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-26 : 12:16:27

To get some real help, we are going to need to know what your stored proc is doing. Odds are, it could be rewritten to take more than one "record" at a time.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-26 : 12:29:20

how about:


DECLARE @LoopTotal int, @Current int, @Total int, @emp_number varchar(13), @NumToProcess int
-- Set the number of records you want to process per batch
SELECT @NumToProcess = 100 
-- Find out how many there are to process in table
SELECT @Total = count(emp_number)
FROM tbl_emp 
WHERE status='NEW' 
-- Verify you have at least as many as you want to process, otherwise only process what you have
SELECT @LoopTotal = CASE WHEN @Total >= @NumToProcess THEN @NumToProcess ELSE @Total END

-- Loop through records
SELECT @Current = 1
WHILE (@Current <= @LoopTotal)
BEGIN
	-- Get emp_number to process
	SELECT top(1) @emp_number = emp_number
	FROM tbl_emp
	WHERE status='NEW'

	PRINT @Current
	-- process emp_number
	EXECUTE usp_emp_calc @emp_number

	PRINT 'emp_number-' + @emp_number + ' processed'
	-- increment counter
	SELECT @Current = @Current + 1
END
PRINT 'No of emps processed - ' + cast(@LoopTotal as varchar(20))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources