SQL Cursor to use or not to use
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 7, 2009 1:39 PM
UPDATED:
January 7, 2009 8:49 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
