Syntax error on SQL Server

Question

this could be a stupid syntax error but I just keep reading my procedure but i cannot figure out where are my errors.

Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'FOR'.

Here is my code :

alter procedure LockReservation as
DECLARE @edition_id tinyint, @stockid  tinyint;
DECLARE @creservation CURSOR FOR select edition_id from reservation where (date_fin - GETUTCDATE()) <= 12;
open creservation;
while @@fetch_status = 0
BEGIN
    fetch creservation into @edition_id;
    DECLARE @cstock CURSOR 
        FOR select id from stock where edition_id = @edition_id;
    open cstock;
    while @@fetch_status = 0
    BEGIN
        fetch cstock into @stockid;
        select stock_id from location where location.stock_id = @stockid and archivage = 0
        if @@rowcount = 0
        BEGIN
             insert into stocks_reserves(id, date_ajout, usure, suppression, edition_id) 
                    Select id, date_ajout, usure, suppression, edition_id 
                from stock 
                where stock.id = @stockid
        END
    END
    CLOSE cstock
    DEALLOCATE cstock
END
CLOSE creservation
DEALLOCATE creservation

Can somebody help me ?

It's the line where i declare creservation

Dimitri
– Dimitri

2011-03-30 19:26:03 +00:00
Commented Mar 30, 2011 at 19:26 — Dimitri
– Dimitri, Commented Mar 30, 2011 at 19:26

Joe Stefanelli · Accepted Answer · 2011-03-30 19:26:30Z

13

Don't use the @ symbol in your cursor names.

answered Mar 30, 2011 at 19:26

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manngo Over a year ago

Why? I accept that it works, but where is it documented?

Joe Stefanelli Over a year ago

@Manngo The documentation clearly shows that there is no "@" used for a cursor name. learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Manngo Over a year ago

The documentation doesn’t use @, but it doesn’t preclude it either. The thing is, you can declare and set a cursor variable separately: declare @something cursor; set @something = … ; but not in the same statement, which is something of a surprise.

Raj More · Accepted Answer · 2011-03-30 19:35:55Z

3

Get rid of the cursor - use a set based solution.

Basically you are doing this:

insert into stocks_reserves
(id, date_ajout, usure, suppression, edition_id) 
Select id, date_ajout, usure, suppression, edition_id 
from stock 
where stock.id in
(
    select stock_id 
    from location 
    where location.stock_id in
    (
        select id 
        from stock 
        where edition_id in
        (
            select edition_id 
            from reservation 
            where (date_fin - GETUTCDATE()) <= 12
        )
    )
    and archivage = 0
)

You can replace the IN with an exists to process the insert faster.

Better still, do INNER JOIN for possibly the best performance.

edited Mar 30, 2011 at 19:35

answered Mar 30, 2011 at 19:24

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

2 Comments

Adi Over a year ago

+1 for this approach. Don't know if it really works for this particular case, but Dimitri can confirm. Anyway, the best is to avoid cursors as much as possible for best performance. the drawback is that debugging is way harder...

Manngo Over a year ago

OK, Cursors are problematic, but the question was about a syntax error, not how to improve the code.

Adi · Accepted Answer · 2011-03-30 19:28:00Z

1

Name your cursor creservation instead of @creservation

answered Mar 30, 2011 at 19:28

Adi

5,2416 gold badges48 silver badges60 bronze badges

Comments

Steve Mayne · Accepted Answer · 2011-03-30 19:28:37Z

1

Drop the @ symbol before your cursor name in the DECLARE @cstock CURSOR statement

answered Mar 30, 2011 at 19:28

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

Collectives™ on Stack Overflow

Syntax error on SQL Server

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related