SQL Server 上的语法错误

发布于 2024-10-29 03:07:47 字数 1138 浏览 2 评论 0原文

这可能是一个愚蠢的语法错误，但我只是继续阅读我的程序，但我无法弄清楚我的错误在哪里。

消息 156，级别 15，状态 1，第 41 行
关键字附近的语法不正确 “为了”。

这是我的代码：

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

有人可以帮助我吗？

原文

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 ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静待花开 2024-11-05 03:07:47

不要在光标名称中使用@符号。

回复收藏 0 原文

雨轻弹 2024-11-05 03:07:47

摆脱光标 - 使用基于集合的解决方案。

基本上您正在这样做：

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
)

您可以将 IN 替换为存在以更快地处理插入。

更好的是，执行 INNER JOIN 以获得最佳性能。

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.

回复收藏 0 原文