cursors - 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 2008 Forums

Transact-SQL (2008)

cursors

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 09:56:23

Cursor 1:
select field1, field2, field3...
from table1, table2, table3, etc...
into @field1, @field2, @field3, ...

Cursor 2:
select field1, field2, field3...
from table15, table20, table35, table25
--UPDATED
WHERE field1 = @field1
etc...
into @chldfield1, @chldfield2, @chldfield3, ...

Cursor2 is within Cursor1

How is it possible to not use these cursors or at least not to use cursor2 for now?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-07 : 10:00:16

how is cursor 2 related to cursor 1 ?

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 10:01:01

Depends a lot on what you are doing with them
There is no dependency here between the 2 cursors so they shouldn't be nested.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 10:03:18

How many posts is this?

You aren't going to get anywhere by asking how to recode cursors.
It's a bit like saying (in fact very like saying) I have an application - how do I improve it?

I could give a nethod to get rid of any explicit cursor but it wouldn't help. It's just replacing the same processing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 10:08:12

It is like:
foreach record in cursor1, go through the records in cursor2 and do something like
insert into tblX(Field1, Field2, Field3)
values(123, @chldField1, @Field1)
...

Note that I have updated the initial post to show a where clause.
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 10:13:20

something like this

with cte as (<query from cursor1>)
insert tblX(Field1, Field2, Field3)
select 123, a.chldField1, a.Field1
from (<query crom cursor2) a
join cte b
on a.field1 = b.field1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 10:20:07

This seems to be joining the two datsets.
Am I right?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 10:23:20

AND:
How do I check something like:
IF @chldField1 = 'field value in cursor2'
AND @Field1 = 'file value in cursor1'

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 10:30:39

That's control of flow - probably changed into a case statement somewhere or maybe part of a join.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 10:34:09

quote:
Originally posted by nigelrivett

That's control of flow - probably changed into a case statement somewhere or maybe part of a join.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Did you see my initial post which I updated with --UPDATED
?, if so, then does your solution in sql still applies the same?
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-07-07 : 11:04:00

quote:
Originally posted by nigelrivett

something like this

with cte as (<query from cursor1>)
insert tblX(Field1, Field2, Field3)
select 123, a.chldField1, a.Field1
from (<query crom cursor2) a
join cte b
on a.field1 = b.field1
This seems to be similar to:
1- placing the result of cursor1 into say #tblcursor1
2- placing the result of cursor2 into say #tblcursor2
3- joining #tblcursor1 and #tblcursor2
via the joined field i.e. field1

Am I right?

Thanks

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 11:05:24

Yes - don't now what it means though.
Usually to rewrite cursors you need to look at what they are doing at a high level not try to replace the detail - that will just end up trying ot do row by row processing which is the underlying issue.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources