2 fixed code formatting
source | link

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:

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

END CLOSE BU_cursor DEALLOCATE BU_cursor

SELECT * FROM @K

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

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
1
source | link

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