Sure, sure if you’re even slightly older than me “the cursor” was the unruly kid at school with a slightly skewed moral compass. However, if you’re like me, and reside on the Great Timeline of Technology, being privy to both the old world and the new information-age world you realize that the cursor can mean a few other things.
Today we’re talking about t-sql.
Ok let me out myself here. I hate cursors. A lot.
They are bloated, complex, and use entirely too much code. I get it though. Sometimes you just have to use them. And I get it that they have their place. Cool. However here is handy way to NOT have to use one though.
If you need to loop through a simple set of data and perform an action on each row you can easily use a table variable in t-sql. This is my GOTO .. get it? .. GOTO? .. ahem, method for looping through data in t-sql.
[sql]
declare @temptable table (
rowid int IDENTITY (0, 1)
, data_column varchar (55)
)
declare @icount int
declare @rows int</code>
set @icount = 0
set @rows = (select count(*) from @temptable)
while @icount <= @rows – 1
begin
‘do processing here
‘here you can evaluate each row in your temptable where it’s identity col matches the current icount var
select data_column from @temptable where rowid = @icount ‘This allows you to select the data and do anything you want with it.
set @icount = @icount + 1
end
[/sql]
If you can still read this then you must be some kind of developer. Even if you are though, I’m still gonna s’plain it Lucy.
What we are essentially doing here is assuming you’ve already put some data into a var called @temptable. Then our while-block allows you to look through it and perform an action on each row. You can also nest loops too. I’ve found all of this quite handy on numerous occasions. Hope you do too!
P.S. – Keep your result-sets relatively small so the DBA doesn’t scream at you 🙂