Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

cursorssql-server-2008

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

I have to:

Create a table variable (virtual in-memory table) with columns that match stored procedure SP_Report resultset
Declare a cursor that contains all business units and then the cursor should loop over the BU's
For each BU, execute a stored procedure called SP_Report and insert the data into the table variable
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.

Best Answer

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

Related Solutions

Sql-server – How to check for a null or empty table-valued parameter

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – How to check for a null or empty table-valued parameter

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Related Question