AB
asked on
Nested cursor in SQL
i wan to run a code for each database and loop through it so i wrote a sql using curosr for one database but not able to put in loop for all the database my code format is like below can anyone help me to put below query in loop for all database.
Declare @variable
Declare ........cursor for select from table where clause order by
open cursror
fetch next from cursron into @variable
while @feetch status=0
Begin
set @ sql = statement
exec
fetch next from cusror into @variable
end close tablecursor
Declare @variable
Declare ........cursor for select from table where clause order by
open cursror
fetch next from cursron into @variable
while @feetch status=0
Begin
set @ sql = statement
exec
fetch next from cusror into @variable
end close tablecursor
Yes, please find an alternative to using a cursor, let alone a nested cursor. There are so many other ways in which to do iterations other than a cursor!
Do not use cursors , they are bad for performance. Lets us know what you want to achieve. We can try for SET based approach. There are multiple options we can use.
-Also if thats not possible use below-
Hope it helps !
-Also if thats not possible use below-
DECLARE @Col1 AS VARCHAR(100)
IF OBJECT_ID('tempdb..#final') IS NOT NULL
DROP TABLE #final
SELECT * INTO #final FROM YourTable
WHILE EXISTS ( SELECT TOP 1 1 FROM #final )
BEGIN
SELECT TOP 1 @Col1 = col1 FROM #final
/* Your processing will come here */
DELETE FROM #final WHERE
ENDHope it helps !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@all --- i know there are lots of script of rebuilding indexes but as a beginner and trying to learn something i was trying myself to write a code that will rebuild indexes on the avg fragmentation which is higher that 30 on one database but i want to put all database in loop to run for maintainence . So i gave a sample script that i coded . Can you please help on that . I m learners so curosor and while loop is confusing. :(
ASKER
In this case i believe i have to use all database and table on each database so i m bit lost here
i was trying myself to write a code that will rebuild indexes on the avg fragmentation which is higher that 30 on one database but i want to put all database in loop to run for maintainenceNo need to reinvent the wheel. You can learn from Ola's script.
And cursors are bad because they create locks and require memory to load. WHILE loops do not load records into memory, and they do not create locks. A cursor should only be used after careful consideration of the alternatives, and profiling those alternatives to compare performance. And in some cases, the cursor should be avoided regardless of performance -- those cases where blocking must be avoided.
The usage of a Cursor itself doesn't cause locks. What can cause locks is the bad usage of a Cursor but this is also true for any query.
http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx