Skip to content

Instantly share code, notes, and snippets.

@wqweto
Created Apr 20, 2020
Embed
What would you like to do?
Writing clever cursor loops in T-SQL (not using the hot-mess from BOL)
IF OBJECT_ID('dbo.zero_ten') IS NOT NULL DROP TABLE dbo.zero_ten
GO
SET NOCOUNT ON
CREATE TABLE dbo.zero_ten (id INT PRIMARY KEY)
INSERT dbo.zero_ten(id)
SELECT 0
UNION ALL
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY m.severity)
FROM sys.messages m
SELECT *
FROM dbo.zero_ten
DECLARE @Errors TABLE (
ErrNumber INT
, ErrSeverity INT
, DynSql NVARCHAR(MAX)
)
DECLARE @CrsDSql CURSOR
, @IterDynSql NVARCHAR(MAX)
SET @CrsDSql = CURSOR FAST_FORWARD FOR
SELECT N'
DECLARE @Atmosphere INT
SELECT @Atmosphere = 1 / ' + CONVERT(VARCHAR(50), id) AS DynSql
FROM dbo.zero_ten
ORDER BY id
OPEN @CrsDSql
WHILE 1=1
BEGIN
FETCH NEXT
FROM @CrsDSql
INTO @IterDynSql
IF @@FETCH_STATUS <> 0 BREAK
BEGIN TRY
RAISERROR (@IterDynSql, 10, 1) WITH NOWAIT
EXEC dbo.sp_executesql @IterDynSql
END TRY
BEGIN CATCH
INSERT @Errors
SELECT ERROR_NUMBER(), ERROR_SEVERITY(), @IterDynSql
END CATCH
END
CLOSE @CrsDSql
DEALLOCATE @CrsDSql
SELECT *
FROM @Errors
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment