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 Mar 30 '11 at 19:26 — Dimitri, Mar 30 '11 at 19:26

score 13 · Accepted Answer · answered Mar 30 '11 at 19:26

13

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

answered Mar 30 '11 at 19:26

Joe Stefanelli

132,803
19
237
235

Why? I accept that it works, but where is it documented? – Manngo Aug 20 '19 at 06:15
@Manngo The documentation clearly shows that there is no "@" used for a cursor name. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017 – Joe Stefanelli Aug 20 '19 at 13:21
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. – Manngo Aug 20 '19 at 22:49

Raj More · Answer 2 · 2011-03-30T19:35:55.180

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 '11 at 19:35

answered Mar 30 '11 at 19:24

Raj More

47,048
33
131
198

1

+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... – Adi Mar 30 '11 at 19:38
OK, Cursors are problematic, but the question was about a syntax error, not how to improve the code. – Manngo Aug 20 '19 at 06:17

score 1 · Answer 3 · answered Mar 30 '11 at 19:28

1

Name your cursor creservation instead of @creservation

answered Mar 30 '11 at 19:28

Adi

5,113
6
46
59

score 1 · Answer 4 · answered Mar 30 '11 at 19:28

1

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

answered Mar 30 '11 at 19:28

Steve Mayne

22,285
4
49
49

Syntax error on SQL Server

4 Answers4

Linked