Search This Blog

Wednesday, November 9, 2011

SQL Server - Simple Cursor Example

A very simple cursor to add the integers from a table variable. The following query declares a table
variable with single column, inserts 3 integers into the table. Next, the cursor is declared and it
adds up the integers and prints the total.
 
--Query Begins--
--Declare table to store integers with single column
DECLARE @NumTable TABLE (Number int NULL)

--Inserts 3 integers into the table
INSERT INTO @NumTable (Number) VALUES (10)
INSERT INTO @NumTable (Number) VALUES (20)
INSERT INTO @NumTable (Number) VALUES (30)

--Declare the variables
DECLARE @Num INT
DECLARE @Total INT = 0

--Declare the cursor
DECLARE Cur CURSOR FOR
SELECT Number FROM @NumTable

--Open the cursor
OPEN Cur
FETCH NEXT FROM Cur INTO @Num

WHILE @@FETCH_STATUS = 0

BEGIN

--Add the integers
SELECT @Total = @Total + @Num

FETCH NEXT FROM Cur INTO @Num

--Close the cursor
END
CLOSE Cur
DEALLOCATE Cur

--Select the total
SELECT @Total
--Query Ends--

No comments:

Post a Comment