5

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.

  • 2
    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. – Mikael Eriksson Mar 13 '12 at 6:30
  • @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. – Pankaj Mar 13 '12 at 6:45
  • 2
    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. – Mark Storey-Smith Mar 13 '12 at 7:35
  • @Mark I have edited my question with all information. Please Help. – Pankaj Mar 13 '12 at 8:31
8

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
  • Great, It worked for me. Need some more help, will let you know ASAP Thanks a ton. :) – Pankaj Mar 13 '12 at 10:36
  • Would like to give u as many points as i can. Thanks a ton. – Pankaj Mar 23 '12 at 8:36
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
0

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

Using Cursor in SQL Server

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.