SQL Cursor to use or not to use

290 pts.
Tags:
SQL
SQL Cursors
SQL scripts
T-SQL
Hi, I've been told to never use cursor in SQL, because of possibility of memory leak, etc. But I have no idea of how to implement my script without them. My script doesn't execute anything on the database, it simply use cursor to create another TSQL script that does the execution. EG. open cursor to get all the user database get the name of the database while cursor isn't empty print 'sp_detach_db ' + db_name get next name close cursor Does anyone know to how to do this without cursor? I am also using TSQL script with nested cursor which also doesn't execute anything on the database but only to print executable TSQL statement, should I be doing this? Thanks in advance.
ASKED: January 7, 2009  1:39 PM
UPDATED: January 7, 2009  8:49 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The basic rule of thumb is to never use a cursor. Its not because of a memory leak, as there isn’t one; there is a performance issue with using cursors.

SQL Server handles its data in recordsets. It is designed to more efficiently process data in recordsets than row by row.

In the case of your example.

<pre>SELECT ‘exec sp_detach_db ‘ + name
from sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’)</pre>

Will spit out the code that you need without a cursor.

There are cases when cursors are the best option for example when you want to run a stored procedure for every row in the recordset a cursor will be the best way, unless you can modify the stored procedure to accept an XML document or User Defined Data Type of TABLE as an input parameter then be able to run the entire thing in a single batch.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following