declaring cursor to loop over some values in SQL server 2008 R2

Question

I have some business unit such as India, International, US, UK. I have to:

1. Create a table variable (virtual in-memory table) with columns that match SPROC SP_Report resultset.
2. Declare a cursor that contains all business units and then the cursor should loop over the BU's.
3. For each BU, execute a stored procedure called SP_Report and insert the data into the table variable.
4. Finally I have to select the columns from the table variable + timestamp + BU, insert them into another table and clear the table variable.

    declare @K table (BU nvarchar(max), K nvarchar(max),Y money, A money, D money, YP money)
    declare @FY int
    declare @BU nvarchar(max)

    INSERT INTO @K
    (BU,K,Y,A,D,YP) 
    EXEC dbo.SP_Report '2012', 'India'

    SELECT * FROM @K

This code gives me the result of the table variable. Now I have to use it cursor, which I don't know how to. How I can solve the same.

Answer 1

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K