Basic Example Of Using SQL Server Cursors

As a DBA on both MSSQL (Microsoft SQL Server) and MySQL databases one of the tasks that I often have to do is to write adhoc reports.  On ocasio, to complete the report you have to use CURSORS so that you can 'step through' a resultset (result of a query) and perform various tasks on evey row.  Thankfull both SQL Server and MySQL include support for Cursors (though MySQL is currently limited to non-updateable cursors).  This entry contains a very basic example of using a Cursor to step through a result set and append two columns (forename and surname) to a variable.  At the end a result set is produced that contains one row of commar separated names.

IF EXISTS (SELECT * FROM sysobjects WHERE
       id = OBJECT_ID(N'[dbo].[p_testproc]') AND

        OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[p_testproc]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE dbo.p_textproc

AS

BEGIN

 -- Declare Vars

 DECLARE @Name VARCHAR(255)

 -- Declare Cursor

 DECLARE lookupCur CURSOR FOR SELECT SurName, Forename FROM Name WHERE SurName = 'Mapes'

 -- Open Cursor

 OPEN lookupCur

 -- Declare Local Vars to load Cursor records into

 DECLARE @SurName VARCHAR(50)

 DECLARE @Forename VARCHAR(30)

 -- Load first row from Cursor

 FETCH NEXT FROM lookupCur INTO @SurName, @Forename

 WHILE (@@FETCH_STATUS <> -1 ) -- Check for Final Row

 BEGIN

   IF ( @@FETCH_STATUS <> -2 ) -- Check for Error

   BEGIN

     SET @Name = @Name+@SurName+' '+@Forename+', '

   END

 -- Load next row into the Cursor

 FETCH NEXT FROM lookupCur INTO @SurName, @Forename

 END

 -- Close and Deallocate

 CLOSE lookupCur

 DEALLOCATE lookupCur

 -- Return the list of names in format SurName Forename, ...

 SELECT @Name

END