SQL结合WITH子句和Cursor-SQLcombineWITHclauseandCursor

4  

Well, aside from the logic of your code (for me - there is no need of cursor here at all), you should just move CTE declaration closer to the place where you're using it.

好吧，除了代码的逻辑（对我来说 - 根本不需要光标），你应该将CTE声明移到你正在使用它的地方。

;with [MyTable] as
(
    Select SubGUID, color from dbo.Cars with(nolock) where color ='blue'
)
insert into @TOP10 (Cat,SubGUID)    
select color,SubGUID from [MyTable]
where SubGUID=@Sub

But really, all your code could be replaced with:

但实际上，您的所有代码都可以替换为：

declare @TOP10 table (Cat Nvarchar(max),SubGUID uniqueidentifier)

insert into @TOP10 (Cat,SubGUID)
select color,SubGUID 
from dbo.Cars with(nolock) 
where 
    color ='blue'
    and SubGUID in (select SubGUID from dbo.Sub with(nolock) where year=2016)

select * from @TOP10

0  

I agree with @AndyKorneyev, but for the sake of experiment:

我同意@AndyKorneyev，但为了实验：

declare @crTest CURSOR,
        @name VARCHAR(1000),
        @type VARCHAR(1000)

set @crTest = CURSOR FAST_FORWARD FOR
    WITH cteObjs AS (
        SELECT TOP 10 NAME, type
        FROM sys.objects o
        )
    select name, type
    from cteObjs


OPEN @crTest

FETCH NEXT FROM @crTest
INTO @name, @type

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name
    PRINT @type

    FETCH NEXT FROM @crTest
    INTO @name, @type
END

CLOSE @crTest
DEALLOCATE @crTest

0  

Don't want to repeat above snippets but be aware that WITH clause result set must be processed directly at it's end.

不想重复上面的代码片段，但要注意必须直接在其结尾处理WITH子句结果集。

Unlike your @tableVar, the scope of the result set that is returned by cte is not valid within complete batch and therefore must be followed by SELECT, INSERT, UPDATE or DELETE statement. (for details and examples see: https://msdn.microsoft.com/en-us/library/ms175972.aspx or follow one of the other answers).

与@tableVar不同，cte返回的结果集的范围在完整批处理中无效，因此必须后跟SELECT，INSERT，UPDATE或DELETE语句。 （有关详细信息和示例，请参阅：https：//msdn.microsoft.com/en-us/library/ms175972.aspx或遵循其他答案之一）。

e. g.

即G。

    --> declare var: 
    declare @tableVar table (colOne int, colTwo nvarchar(30)
    );

    --> use cte: 
    with preOne as(select carId, color from pcUser with (nolock)
    )
    --> directly followed by insert:
    insert into @tableVar(userId, logInName)
      select colOne, colTwo from @tableVar;

    --> use var in cursor or anywhere else in the batch

Though I'm not exactly sure what you aim at, perhaps a short join could do the trick here:

虽然我不确定你的目标是什么，也许短暂的联接可以在这里做到这一点：

select a.[SubGUID]
from [dbo].[Sub] as a with (nolock) 
    inner join [dbo].[Cars] as b with (nolock) on a.[SubGUID] = b.[SubGUID]
where a.[year] = 2016 and b.[color] = 'blue';

0  

In the end I used temp tabeles like #TOP10. Which I droped after the query. As soon as I got rid of the with [MyTable] as part I had no more problems with the parameters.

最后我使用了像＃TOP10这样的临时标签。在查询之后我下垂了。一旦我摆脱了[MyTable]作为一部分我没有更多的参数问题。

Thank you all lot.

非常感谢你们。