CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
Johnny Spindler
Johnny Spindler

Reputation: 456

SQL combine WITH clause and Cursor

How is it possible to use declared parameter, a WITH clause and a cursor in one Query

declare @TOP10 table (Cat Nvarchar(max),SubGUID uniqueidentifier)
declare @Sub uniqueidentifier
declare GUID_Cursor cursor FOR
(select SubGUID from dbo.Sub with(nolock) where year=2016)
;
with [MyTable] as
(
Select SubGUID, color from dbo.Cars with(nolock) where color ='blue'
)


open GUID_Cursor
fetch next from GUID_Cursor into @Sub
while @@FETCH_STATUS=0
begin
insert into @TOP10 (Cat,SubGUID)

select color,SubGUID from [MyTable]
where SubGUID=@Sub

fetch next from GUID_Cursor into @Sub

end
close GUID_Cursor
deallocate GUID_Cursor

select * from @TOP10

I just cant find a way tu open the cursor after using the WITH clause.

Can anyone help me?

Upvotes: 0

Views: 3314

Answers (4)

Johnny Spindler
Johnny Spindler

Reputation: 456

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.

Thank you all lot.

Upvotes: 0

Markus
Markus

Reputation: 16

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

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).

e. 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';

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9334

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

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

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

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.

;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

Upvotes: 4

Related Questions