Thursday, February 21, 2013

T-SQL Cursor example

--------------------------CURSOR CODE BEGIN------------------------------
DECLARE @id INT
DECLARE @doc_id INT
DECLARE @review_count INT


DECLARE MyCursor CURSOR FAST_FORWARD FOR
SELECT
 doc_id,
 SUBSTRING(short_desc, 6, 6) AS id
FROM tbl_client_doc cd
WHERE 
cd.doc_type_id = 18 
AND 
cd.short_desc LIKE "XYZ: %'
AND 
cd.note_desc = ''
ORDER BY
cd.doc_type_id DESC
OPEN TarCursor

FETCH NEXT FROM MyCursor INTO @doc_id, @id

WHILE @@FETCH_STATUS = 0
BEGIN
SET @review_count = (SELECT COUNT(answer_id) FROM tbl_review_gen WHERE id = @id)
IF @review_count > 0
BEGIN
PRINT 'DELETE FROM tbl_client_doc WHERE doc_id = ' + CONVERT(varchar(10), @doc_id)
END
FETCH NEXT FROM MyCursor INTO @doc_id, @id    ---- Have to move to the next row
END
--CLOSE MyCusor
DEALLOCATE MyCursor
GO
--------------------------CURSOR CODE END-------------------------------


Post a Comment