Looping through a list of values

Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)In T-SQL? Even heart of a cursor?|||The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE|||Originally posted by DoktorBlue
In T-SQL? Even heart of a cursor?

i'll have to look that up tonight. Never used cursors before.

thanx dok|||With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.

See DECLARE CURSOR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) for the syntax and soem examples in SQL Server 2000. Version 7 is similar.|||Originally posted by nano_electronix
Hi all

How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.

cheers
james :)

Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..|||Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck|||give us more details, maybe there is no need to do the loop at all.|||Hi all

I did it... woohoo, thanx for all your help guys.
This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.

Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.

Cheers
James

PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah :D

CREATE PROCEDURE GenerateTimesheets
(
@.JobID decimal
)
AS
BEGIN
-- Constants
DECLARE @.AllocatedTime decimal

-- Used for looping through recordset with CURSORs
DECLARE @.AccumulatedTime decimal -- Accumulated time spent on a particular job
DECLARE @.SpentTime decimal -- Time spent on each subtask of a job
DECLARE @.TimesheetDate datetime
DECLARE @.JobUpdateID decimal
DECLARE @.TimesheetID decimal
DECLARE @.TimesheetType int

-- Initialize variables
SELECT @.AllocatedTime = allocatedTime
FROM Job WHERE jobID = @.JobID
SET @.AccumulatedTime = 0
SET @.TimesheetType = 1

-- Define CURSOR for each distinct day
-- Because timesheets are generated on a daily basis.
DECLARE date_cursor CURSOR FOR
SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
FROM jobUpdate WHERE jobUpdate.jobID = @.JobID

OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
-- Create timesheets for each day
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Create a new timesheet for each day
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
-- Return the new timesheetID for reference
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Create new cursor to loop through jobupdates for a particular day
DECLARE jobupdate_cursor CURSOR FOR
SELECT spentTime, jobUpdateID
FROM jobUpdate
WHERE @.TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
AND jobID = @.JobID

OPEN jobupdate_cursor
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID

-- Loop through and process all jobupdates for the day
-- creating new timesheets for CT and TA if necessary
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.AccumulatedTime = @.AccumulatedTime + @.SpentTime

------------
-- if currently in standard time phase
------------
if @.TimesheetType = 1
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id

END
ELSE IF @.AccumulatedTime >= @.AllocatedTime
BEGIN
-- Create new completion time timesheet
SET @.TimesheetType = 2
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

-------------
-- if currently in completion time phase
-------------
ELSE IF @.TimesheetType = 2
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

--------------
-- if currently in timesheet approval phase
--------------
ELSE IF @.TimesheetType = 3
BEGIN
-- Update the current job with the timesheet refernce id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END

FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID


END
CLOSE jobupdate_cursor
DEALLOCATE jobupdate_cursor

FETCH NEXT FROM date_cursor
INTO @.TimesheetDate

END
CLOSE date_cursor
DEALLOCATE date_cursor

END|||Hi James, very impressive! You are a fast learner.|||Originally posted by DoktorBlue
Hi James, very impressive! You are a fast learner.

Thanx dok !!!
I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.

You have been very helpful, thanx again.

Cheers
James|||I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.

If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.|||I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.

I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.

Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.

blindman|||everyone learns from mistakes, won't take long ;-)|||Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.|||Taking a step into the cursor world is too often a step in the wrong direction. Cursors are too often a crutch used by procedural programmers. James needs to learn set-based processing, and their are a lot of individuals on this forum who would be happy to help him.

You may be impressed by volumes of code, but I'm impressed by short, elegant code that is simple, easily understood, well commented and readily debugged.

Inside every large program is a small program screaming to get out.

blindman|||Why don't you let it out, I'm curious to see whether you can add practise to you theory.|||Well, I've managed to shorten a lot of your code, and I and other members will help James if he asks.

I'll help you if you ask, too.

blindman|||You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

About cursors: they are a usual mean to express functionality, and they are not bad at advance. It up to the user to use or abuse this functionality, like you can also easily write a "query from hell". The main point about cursors is, that the user takes responsibility of the execution plan from the Analyzer.

Blindman, if you want to respond, please do me a favour and focus on the issue of this thread, not other threads, not aiming at me, just show your short elegant solution, which would be a real contribution.|||Originally posted by DoktorBlue
Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.

What you say is true to a certain extent. There are view few cases where Cursors are needed and I believe that if people paid more attention to database concepts they would invariably find a set based solution to most of their SQL problems.

As the previous poster stated, it is hard to follow the logic of the cursor, and to make matters more complicated he has also used a nested cursor!

What we need is some CREATE TABLE statements and a few example records and I'm almost 100% sure that one of us here will come up with a much more efficient set based solution.

My 2 cents.|||I am also totally in agreement with blindman .
I would rather not use cursors ... let alone nested cursors , if there was an alternative way.

You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

well , DoktorBlue , whats all the fighting for ... we are all here to share our knowledge .. dosent matter who is helping whom ... till the other guy gets the solution. Whats the problem in a little friendly discussion.|||I agree with DoktorBlue that you shouldn't continually jump onto threads and say that someone's solution is bad without offering a solution. The anti-cursor evangelists have done the same to me. I asked for a solution but they didn't provide one.

I'm actually a junior TSQL guy but even I can see that maybe the outer loop of this guy's code could be a WHILE loop looping through days (most timesheets don't skip days in the middle) but it would appear some folks are too scared to risk their lofty positions by actually suggesting some code. You'd think that this would be a simple enough of an example that they could put something out there....nope.

How do you get those stars under your name anyway...|||One more thing ...

If it is really a growing problem that cursors are often used inappropriately (because newbies are procedural rather than focused on how datasets work or for whatever reason), why not write a quick article and post it on the web (if it doesn't exist already). The article would show "practical" examples of common mistakes and how to avoid them.

That way, when you see samples of inappropriate cursor usage in these forums you can just tell the poster "You may benefit from this article" and give'em the URL. I could personally benefit from this article right now.

It's cool that folks exist out there like you guys who know the theories of what is good and bad but if nobody can explain in practical terms when they apply then...

Sorry for the rant.|||bill_dev,

I and other members have offered to provide more assistance with this problem, but we need more details about the requirements. We are not going to spend our time developing solutions (for free) without requirements. That is just chasing shadows.

Truth is, I have spent a lot of time looking over James' code, and as I stated quite a while back the first problem is that it is difficult to figure out what the code is supposed to be doing. Readability is important, especially if you have ever inherited a project, or even had to debug or modify something written six or twelve months before.

Cursors are crutches. They are slow, and awkward, and if you use them too much or too often they get painful. But like crutches, sometimes they are absolutely necessary and nothing else will do.

blindman|||I have to say I am in the anti-cursor faction. If you have a cursor on your system, try running it with task manager running, and watch the cpu graph. Now imagine that cursor being run by 5 or 6 other people at the same time. Truth is cursors are not scalable.
Another reason I am against cursors is that it promotes putting business logic directly on the database. If you know you are only going to have a very few records in these tables (under say 1000), then ok, the cursor can be used if there is no other way. But if you think, or even dream you will be going to hundreds of users, then the business logic has to come off the database. This is a job for application servers. Think about it this way. You can have many application servers, but you are stuck with one database.
To top it off, in many companies, you are not only stuck with one database server, but you have to share that server with all the other applications' databases. The performance on these servers is generally equal to the worst performing application.

As far as improvements in this particular procedure go, the only thing I have so far is to use select @.@.identity after the timesheet inserts, rather than select the new identity right off the table. If you have a million timesheets, you will see the performance degrade. If you have no index on the fields in the where clause, you guarantee that you have performance problems. Just things to watch for.

And, yes, I am still thinking about ways to eliminate the cursor entirely. So far, my best bet is to use a perl or VB script to do the actual logic.|||Originally posted by snail
Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck

snail's suggestion is good. Using a temp table is simpler than a cursor.

Here is a complete example:

-- Update area codes for people in the 415 area code
-- Set area code to 234
USE pubs
GO

-- Show values before update
SELECT au_id, phone
FROM authors
ORDER BY au_id

DECLARE @.error int
DECLARE @.authorCt int
DECLARE @.loopCt int

DECLARE @.authorsToUpdate TABLE (
EntryID integer IDENTITY(1,1),
au_id varchar(11),
phone char(12)
)

INSERT INTO @.authorsToUpdate (
au_id,
phone
)
SELECT
au_id,
REPLACE(phone, '415 ', '234 ')
FROM authors
WHERE phone LIKE '415 %'

SELECT
@.error = @.@.error,
@.authorCt = @.@.rowcount

-- Error check

IF @.authorCt > 0 BEGIN
SET @.loopCt = 0

BEGIN TRAN

WHILE @.loopCt < @.authorCt BEGIN
SET @.loopCt = @.loopCt + 1

UPDATE a
SET a.phone = u.phone
FROM authors AS a
JOIN @.authorsToUpdate AS u
ON a.au_id = u.au_id
WHERE u.EntryID = @.loopCt

-- Error check
END

-- Show values after update
SELECT au_id, phone
FROM authors
ORDER BY au_id

ROLLBACK TRAN
-- COMMIT TRAN
END
GO|||Cool. Very useful...Thanks|||Finally somebody with more than a story, but a proposal. Bravo!|||Thanx all for all your feedbacks.

I haven't looked this up for the last 2 days, so i missed all the discussions.

I think the suggestion of using temporary table is probably the way to get rid of cursor for certain cases. For the logic that i am trying to implement, using cursor is probably necessary and the most intuitive, but because the small amout of records that i have to process, there is no real performance issue for me.

The program logic that i tried to implement is to basically generate timesheets from jobupdate entries entered by developers. Basically how our company works is like follows.

1. Client submits a request
2. Project manager receives request and allocate Jobs to differnt developers. Each job has an allocated time.
3. Developers will update their job justifying what they did everytime they finish a task for that job, these jobupdates will include the time spent on this task

when ever a job is completed a number of timesheets would be generated from all the jobupdates submitted . for this particular company, they want to generate timesheets on a per job per day basis, what's more, all the jobupdates that exceeds the allocated time will be put into a differnt timesheet. so normally there would only be 1 timesheet per day when all the jobupdates submitted for a particular job are within allocated time. once allocated time is exceeded, there could be a maximum of 3 different timesheets generated for a particular day. Thus the reason that i have to process each entry individual and thus the need for cursor.

Because of this business logic, there is almost no way of avoiding cursor.

Cheers
James

PS: I love you guys, I have learnt a lot from dok and blindman and a number of other people since i joined. dbforums rocks because of you!! :)