Is there alternate way to use cursors in sql without any loops. [Resolved]

Posted by Allemahesh under Sql Server on 8/14/2013 | Points: 10 | Views : 1747 | Status : [Member] [MVP] | Replies : 2
Is there alternate way to use cursors in sql without any loops.




Responses

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
ln SQL Server there is no that much usage of cursors. loops also we rarely use in development. ln most of the scenarios we can achieve the functionality by using simple DML operations such as insert, update, delete, merge.
can you tell me the some scenarios where u wish to use loops/cursors

1) we can use Recursive queries
2) For simple loop kind of queries we can use derived(nested) queries
3) Using User Defined Functions -- refer http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/27/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Alternatives for CURSOR in SQL Server
1) Set-Based Commands
Set-based SQL commands are more efficient because you’re using SQL Server’s highly optimized engine to do your iteration. If you iterate through data yourself, you’re not using the SQL Server storage engine optimally.
In fact set-based commands like SELECT, UPDATE, and DELETE, when applied to tables directly and not in a cursor or WHILE loop, bring you closer logically to your data, precisely because you can ignore the order of the data

2) CTE (Common Table Expressions)
Most of the times CTE's will be useful for recursive queries.....
Ex: To list of the parent-child-grand child chaining we can use CTE

3) Stored Procedure or User Defined Functions
we can say that "set-based commands / CTEs inside T-SQL " is also one of the alternative for CURSOR in SQL Server....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response