Tuesday, 14 March 2017

SQL how not to cursor

While many of us know we should not cursors we often do as quick loops, and this is not a good practice, so I'm going to show you a very quick example of how to create an SQL statement that will create a command list into a temp table and then run it without a cursor to loop them.

First here is how it might look using a cursor.


IF OBJECT_ID('tempdb..#query') IS NOT NULL
DROP TABLE #query;
CREATE TABLE #query
(
ID INT IDENTITY(1, 1) ,
query nvarchar(4000) ,
);

INSERT INTO #query
(query)
select
'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT;
ALTER DATABASE [' + name + '] SET MULTI_USER;'
from sys.databases where database_id > 4

DECLARE @Sql nvarchar(4000)
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
Select Query FROM #query -- table where sql is stored
OPEN Cur
FETCH NEXT FROM Cur INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM Cur INTO @Sql
END
CLOSE Cur
DEALLOCATE Cur;
DROP TABLE #query;


And now here is the same process without using a Cursor to achieve the same results.
Not only does it mean that you are not using a Cursor, but if you look closely it's even a few lines shorter.

IF OBJECT_ID('tempdb..#query') IS NOT NULL
DROP TABLE #query;
CREATE TABLE #query
(
ID INT IDENTITY(1, 1) ,
query nvarchar(4000) ,
);

INSERT INTO #query
(query)
select
'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT;
ALTER DATABASE [' + name + '] SET MULTI_USER;'
from sys.databases where database_id > 4

Declare @sql nvarchar(max)
while exists (select top (1) [query] from #query)
Begin
select top (1) @sql= [query] from #query
exec (@sql)
DELETE TOP(1) FROM #query
END
DROP TABLE #query;


Hopefully, this will help keep your systems as cursor free as possible.

No comments: