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 stored procedure 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.

Code:

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 returns 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.

• Mikael Eriksson: The answer usually is that you should avoid using cursors and that you should try to solve your issue using a set based approach instead. However, sometime a cursor (or loop) has its place. Please have a look here for syntax and some examples.
• PankajMishra: @Mikael I have been instructed to use cursor. Stored procedure i made myself and after passing some value stored procedure is giving me the result now i want to insert the data into a table variable and have to loop over a particular business unit(Which i have already asked in my question). And how can i see the result of the Table variable as it is not in the database. Help. Thanks.
• Mark Storey-Smith: Add the SQL you've written so far to your question + pseudo code for the part you're unsure about. It's not clear what you're trying to achieve.
• PankajMishra: @Mark I have edited my question with all information. Please Help.

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

• PankajMishra: Great, It worked for me. Need some more help, will let you know ASAP Thanks a ton. :)
• PankajMishra: Would like to give u as many points as i can. Thanks a ton.

Answer 2

Can you take this one step further and make the inner query run dynamic sql, something like this:

DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))
DECLARE @cmd varchar(222)
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
DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
 --INSERT @K (BU, K, Y, A, D, YP)
 --EXEC dbo.SP_Report @FY, @BU
 SET @cmd = 'INSERT '+@K+' (BU, K, Y, A, D, YP)'+
 ' SELECT '+@BU+','+@BU+',456,78,99,11111'
 EXEC sp_sqlexec @cmd
 FETCH BU_cursor INTO @BU
END
CLOSE BU_cursor
DEALLOCATE BU_cursor
SELECT * FROM @K

Answer 3

I think, its the best example to understand CURSOR easily..

Using Cursor in SQL Server

Answer 4

simple example to understand cursors:

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;
SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')
OPEN @BusinessCursor;
 FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @msg = '{
 "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
 "Customer": {
 "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
 "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'", 
 }
 }|'
 print @msg
 FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END