SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How get the dynamic sql in to cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

honeyram
Starting Member

India
1 Posts

Posted - 09/23/2005 :  07:19:00  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 09/23/2005 :  07:24:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 SQL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/23/2005 :  07:26:40  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 09/23/2005 :  07:26:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
There's either a contest, or some global database class everyone is taking:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55535
Go to Top of Page

mariob
Starting Member

Croatia
15 Posts

Posted - 09/26/2005 :  07:52:04  Show Profile  Reply with Quote
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).
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/26/2005 :  08:21:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Why do you all find cursors so ugly?

You can use better approach than Cursors

Suppose you have two tables with identical structures and you want to copy the data of one table to other

You 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 by

Insert into Table1(columns) Select columns from Table2

In most cases there is better alternate to Cursors

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mariob
Starting Member

Croatia
15 Posts

Posted - 09/26/2005 :  08:44:08  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/26/2005 :  08:47:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mariob
Starting Member

Croatia
15 Posts

Posted - 09/26/2005 :  08:51:47  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/26/2005 :  08:55:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Not sure I understand what you mean.

I mean in SQL Server Cursors have less implementation

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Antonio
Posting Yak Master

United Kingdom
168 Posts

Posted - 09/26/2005 :  12:19:17  Show Profile  Visit Antonio's Homepage  Reply with Quote
quote:
Originally posted by madhivanan

>>Not sure I understand what you mean.

I mean in SQL Server Cursors have less implementation

Madhivanan

Failing 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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/27/2005 :  00:27:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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 cases
But I see most programmers use Cursor when actually it is not needed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mariob
Starting Member

Croatia
15 Posts

Posted - 09/27/2005 :  01:43:56  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000