A basic SQL Cursor

Hey followers or first time readers,

I thought this blog I would write a simple cursor.  I have been using them a lot lately and thought I might share a simple.  I have used them in past blogs but wanted an entry just about a cursor.

Let's get our temp table set up like my standard blog goes.

--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'


Before we create the cursor let's set up a few variables.

--Setting up variables
DECLARE @Team VARCHAR(20),
    @Founded DATE,
    @City VARCHAR(30),
    @STATE CHAR(2)

Now we can define and cursor and loop through all the values in the temp table.  This is pretty simple and basic.

--Declare the cursor
DECLARE Team_Cursor CURSOR FOR
SELECT Team, Founded, City, State 
FROM #Teams--Opening cursor
OPEN Team_Cursor 
FETCH NEXT FROM Team_Cursor INTO @team, @Founded, @city, @State 
WHILE @@FETCH_STATUS = 0
   BEGIN
       --Outputting the current record in the cursor
        SELECT @team AS '@team', 
                DATEPART(YEAR,@Founded) AS '@Founded', 
                @city AS '@city', 
                @State AS '@State' 

       FETCH NEXT FROM Team_Cursor INTO @team, @Founded, @city, @State
   END

CLOSE Team_Cursor --closing the cursor
DEALLOCATE Team_Cursor --deallocate the cursor


Let's see what this looks like when its outputted.






Hope you learned how to write a simple cursor in this example.

-Robert