Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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
1  
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
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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