For loops in SQL

[udit99]

I want to iterate through a list of key values and run SQL in each iteration based on that value but im not sure how to use the for loop in SQL. Something like this:

declare @test numeric(9)
for @test in (134424,134423,134425)
select count(*) from test_table where test_id=@test
next

 

[ecwcee]

Are you using Microsoft SQL Server? The real fun there is that it doesn't support for loops; you need to duplicate the operation of a for loop using a cursor. Before I post an example of cursor use I'll wait to find out if you're using SQL Server, though

 

[udit99]

Nope, we're using Sybase. I dont know if your solution would be applicable to Sybase but im sure it has cursors too.

 

[ecwcee]

In vague hopes of helping you out, I'll post a framework for using a cursor as I would in SQL Server. My suggestion is to research cursors in Sybase and go from there, but hopefully this will get you going in the right direction:

[COLOR=green]#dir is a temporary table, basically the syntax is DECLARE [i]variable[/i] CURSOR FOR [i]select statement[/i][/color]
DECLARE reader CURSOR FOR SELECT fname FROM #dir
OPEN reader
FETCH NEXT FROM reader INTO @fetcher
WHILE @@FETCH_STATUS=0
BEGIN
	...Do whatever you want in the loop!...
FETCH NEXT FROM reader INTO @fetcher
END

Basically it uses a select statement that returns one column , I know it's possible to use multiple columns but it gets pretty complicated. Inside the loop I've shown above, the @fetcher variable holds the data in the field returned by the select statement. Hope this sheds some light, I will continue to answer questions if I can, good luck!