SQL Server上的语法错误

这可能是一个愚蠢的语法错误,但我只是不停地看我的程序,但我想不出哪里是我的错误。

消息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

有人可以帮我吗?

--------------解决方案-------------

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

摆脱光标 - 使用一组基础的解决方案。

基本上你这样做是:

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的可能是最好的表现。

命名你的光标,而不是creservation的@creservation

@你的光标名字前的符号在DECLARE @cstock CURSOR声明

分类:SQL 时间:2015-03-15 人气:0
本文关键词: SQL,SQL Server 2000中
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.996 (s). 10 q(s)