| Author |
Topic |
|
honeyram
Starting Member
1 Post |
 Posted - 2005-09-23 : 07:19:00
|
| Dear folks, In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation. Declare @sql varchar(5000); set @sql='select * from Role where Role_id in('+ @role_ids +')'; Exec @sql; I want take this record set in to cursor .How to do it.please help me. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-23 : 07:24:55
|
| In SQL Server the thing that can be done with Cursors can also be done without Cursors in more efficient way. Explain what your are trying to do. Probably the thing can be done without Cursors and Dynamic SQLMadhivananFailing to plan is Planning to fail |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-09-23 : 07:26:40
|
oh noooooooo!!!not the curse of the dreaded cursor  why don't you put all the id's in a table variable or a temp table and then join to this table in your query?That way you don't need that ugly cursor or dynamic sql.Duane. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-23 : 07:26:43
|
| There's either a contest, or some global database class everyone is taking:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55535 |
|
|
|
mariob
Starting Member
15 Posts |
Posted - 2005-09-26 : 07:52:04
|
quote:
That way you don't need that ugly cursor or dynamic sql.
Why do you all find cursors so ugly? I find it very elegant and easy to read. I really don't know how they are implemented in MS SQL, but in Firebird they work just perfectly and don't present any major slow-down (50 people running complex cursor queries on production planning software without problems). |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-26 : 08:21:41
|
| >>Why do you all find cursors so ugly?You can use better approach than CursorsSuppose you have two tables with identical structures and you want to copy the data of one table to otherYou can do it with cursor by reading each row, assigning values to variables and insert those values to other table. But the same thing can be acheived easily byInsert into Table1(columns) Select columns from Table2In most cases there is better alternate to Cursors MadhivananFailing to plan is Planning to fail |
|
|
|
mariob
Starting Member
15 Posts |
Posted - 2005-09-26 : 08:44:08
|
quote:
Suppose you have two tables with identical structures and you want to copy the data of one table to other
One would be crazy to use cursor in that situation. But in situations when you need to do certain calculations and IF..THEN..ELSE blocks...how to do it without cursors? Extremely complex queries that are very hard to read and understand?Like I said, I'm totaly new to MS SQL and I don't know how cursors are implemented (Oracle 9i has terrible implementations of cursors). If it has lousy implemetation, then I understand that using cursors is not desirable. But Firebird, for instance, has great implementation of cursors and I really don't know how would I do many things in our software without row-by-row processing from result set. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-26 : 08:47:25
|
| >>when you need to do certain calculations and IF..THEN..ELSE blocks...how to do it without cursors? Case When Col='Value' then 'somevalue' else 'someOthervalue' end>>But Firebird, for instance, has great implementation of cursors But not in the case of SQL Server MadhivananFailing to plan is Planning to fail |
|
|
|
mariob
Starting Member
15 Posts |
Posted - 2005-09-26 : 08:51:47
|
quote:
Case When Col='Value' then 'somevalue' else 'someOthervalue' end
And if that IF..THEN contains another SELECT which also has a need for IF..THEN...you get several nested CASE commands in which you have other complex selects....just like I thought. Very complicated and extremely hard to read. quote:
But not in the case of SQL Server
Not sure I understand what you mean. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-26 : 08:55:02
|
| >>Not sure I understand what you mean.I mean in SQL Server Cursors have less implementationMadhivananFailing to plan is Planning to fail |
|
|
|
Antonio
Posting Yak Master
168 Posts |
Posted - 2005-09-26 : 12:19:17
|
quote:
Originally posted by madhivanan
>>Not sure I understand what you mean.I mean in SQL Server Cursors have less implementationMadhivananFailing to plan is Planning to fail
Depends on what you're working on. For example, if you happen to design a datawarehouse from scratch (highly not recommended) then there will be much use of cursors to do much of the processing.People who think that cursors are bad are generally talking about select statments or easy-ish queries.Don't get me wrong... I agree that cursors should be avoided as much as possible but they are NOT bad.. you just have to know when to and when not to use them.My 2 cents.  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-27 : 00:27:04
|
| >>you just have to know when to and when not to use them.Yes. I think the use of Cursors can be avoided in most casesBut I see most programmers use Cursor when actually it is not neededMadhivananFailing to plan is Planning to fail |
|
|
|
mariob
Starting Member
15 Posts |
Posted - 2005-09-27 : 01:43:56
|
| Then, please be so kind to give cursor-free solution to my query in other thread.My brain is also set to procedural thinking, but I would be happy to change that to set thinking if just someone shows me how to do it, instead everybody are saying "Avoid cursors, they are crap", but nobody acutally shows me how and when it does, it does on extremely simple examples where I wouldn't use cursors any way. |
|
|
|
|
How get the dynamic sql in to cursor