SQL Server 2008 -有嵌套变量的存储过程不能工作

Question

我试图在SQL Server中使用临时表从表中取出id，然后循环通过一年中的12个月…我可以拉生成物。id输出没有问题，但是当我进入到一年中的月份的嵌套循环时，productID变量没有随之移动…

    USE [testDB]
    GO
/****** Object:  StoredProcedure [dbo].[marketInsert]    Script Date: 06/19/2014 21:38:14     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Me
-- Create date: 6/19/2014
-- Description: Insert transaction records for new markets
-- =============================================
ALTER PROCEDURE [dbo].[marketInsert] @marketID int
-- Add the parameters for the stored procedure here
AS
DECLARE @productID int
DECLARE @i int
DECLARE @imax int
DECLARE @year int
DECLARE @NumberRecords int, @RowCount int
set @imax = 12
set @i = 1
CREATE TABLE #Products (
productID int
 )
INSERT INTO #Products (productID) SELECT ID FROM products
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @productID = productID 
FROM #Products
WHERE productID = @RowCount
print @productID --WILL PRINT HERE!!!
WHILE (@i <= @imax) --loop through each month
    Begin
        --print @productID WILL NOT PRINT HERE!!!!!
        --print 'Year: ' + cast(@year as varchar(5)) + 'ProductID: ' + cast(@productID as varchar(30)) + ' Month: ' + cast(@i as varchar(2)) + ' MarketID: ' + cast(@marketID as varchar(2))
    SET @i = @i + 1 
END -- WHILE @i <= imax
SET @RowCount = @RowCount + 1
END    
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
END

Answer 1

适合你

-- =============================================
-- Author:      Me
-- Create date: 6/19/2014
-- Description: Insert transaction records for new markets
-- =============================================
    ALTER PROCEDURE [dbo].[marketInsert] @marketID int
-- Add the parameters for the stored procedure here
AS
DECLARE @productID int
DECLARE @i int
DECLARE @imax int
DECLARE @year int
DECLARE @NumberRecords int, @RowCount int
set @imax = 12
set @i = 1
CREATE TABLE #Products (
productID int , rown
 )
INSERT INTO #Products (productID,rown) 
SELECT ID,row_number() over(order by id)  FROM products
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @productID = productID 
FROM #Products
WHERE rown = @RowCount
print @productID --WILL PRINT HERE!!!
WHILE (@i <= @imax) --loop through each month
    Begin
        --print @productID WILL NOT PRINT HERE!!!!!
        --print 'Year: ' + cast(@year as varchar(5)) + 'ProductID: ' + cast(@productID as varchar(30)) + ' Month: ' + cast(@i as varchar(2)) + ' MarketID: ' + cast(@marketID as varchar(2))

    SET @i = @i + 1 
END -- WHILE @i <= imax

SET @RowCount = @RowCount + 1
END    

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
END 

Answer 2

我不能得到这个工作与临时表…我将尝试像其他人建议的那样使用光标。

Answer 3

按如下方式修改临时表(添加IDENTITY列)

CREATE TABLE #Products (Num INT IDENTITY(1,1), productID int)

现在在While循环

中获取产品ID

SELECT @productID = productID 
FROM   #Products
WHERE  Num = @RowCount

这将工作。

过程

ALTER PROCEDURE [dbo].[marketInsert] @marketID int
-- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
DECLARE @productID int
DECLARE @i int
    DECLARE @imax int
    DECLARE @year int
    DECLARE @NumberRecords int, @RowCount int
    set @imax = 12
    set @i = 1
    CREATE TABLE #Products (Num IDENTITY(1,1),
    productID int
     )
    INSERT INTO #Products (productID) SELECT ID FROM products
    SET @NumberRecords = @@ROWCOUNT
    SET @RowCount = 1
    WHILE @RowCount <= @NumberRecords
    BEGIN
    SELECT @productID = productID 
    FROM #Products
    WHERE Num = @RowCount
    print @productID --WILL PRINT HERE!!!
    WHILE (@i <= @imax) --loop through each month
        Begin
            --print @productID WILL NOT PRINT HERE!!!!!
            --print 'Year: ' + cast(@year as varchar(5)) + 'ProductID: ' + cast(@productID as varchar(30)) + ' Month: ' + cast(@i as varchar(2)) + ' MarketID: ' + cast(@marketID as varchar(2))
        SET @i = @i + 1 
    END -- WHILE @i <= imax
    SET @RowCount = @RowCount + 1
    END    

    -- Insert statements for procedure here
    END

Answer 4

试试这个

ALTER PROCEDURE [dbo].[marketInsert] @marketID int
-- Add the parameters for the stored procedure here
AS
begin 
    DECLARE @productID int
    DECLARE @month int
    DECLARE @year INT = 2014 
    --i set year variable to a default value to 2014
    --if this is null then 2nd print will not print any data
    --create month table
    DECLARE @monthtable table(monthid integer);
    insert into @monthtable 
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
    --declare cursor
    DECLARE cur CURSOR FOR
    SELECT 
    Id, Monthid
    FROM products, @monthtable
    ORDER BY 1,2
    OPEN cur
    FETCH NEXT FROM cur INTO @productID,@month;
    WHILE @@FETCH_STATUS = 0
    BEGIN   
    print @productID --WILL NOW PRINT HERE!!!!!
    print 'Year: ' + cast(@year as varchar(5)) + 'ProductID: ' 
       + cast(@productID as varchar(30)) + ' Month: ' 
       + cast(@month as varchar(2)) + ' MarketID: ' 
       + cast(@marketID as varchar(2))
    FETCH NEXT FROM cur INTO @productID,@month;
    END
    CLOSE cur;
    DEALLOCATE cur;
END;

EXEC [dbo].[marketInsert] 1