这段存储过程有什么问题? - SQL Server应用开发 - - 微软SQL Server专区

社区导航: 专家门诊网络技术操作系统数据库程序设计系统应用考试认证 CIO及信息化站长交流综合交流下载基地 51CTO产品服务

【设为首页 | 收藏本站】

51CTO技术论坛» 微软SQL Server专区 » SQL Server应用开发 » 这段存储过程有什么问题? [ 打印] [ 订阅] [ 收藏] [ 推荐给朋友] [ 本帖文本页]

论坛跳转:

本主题由阿楚于 2008-5-7 13:35 移动该主题悬赏的20无忧币已被全部领完

wiwi

新新人类

帖子 30
精华 0
无忧币 42
积分 62
阅读权限 20

注册日期 2007-8-2

最后登录 2008-5-14

离线

[查看资料] [发短消息] [Blog]

发表于:2008-5-7 13:16　　标题：这段存储过程有什么问题?
＜上一帖 | 下一帖＞

大家帮忙看看这段存储过程有什么问题调试符合要求但代码里调用却给出错误结果还有输入参数@createdate2 varchar(10) 这一行为什么不能注释掉?(注释就报错:  "服务器: 消息 156，级别 15，状态 1，过程 dt_distrbute_shlef2，行 27
在关键字 'AS' 附近有语法错误。")

附件是此过程的文本请大家帮忙看看谢谢了!
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER                                        PROC dt_distrbute_shlef2
@spec varchar(10),
@num varchar(10),
[url=mailto:--@unit]--@unit[/url] varchar(10),
@kind varchar(10),
@createdate2 varchar(10)
--输入参数
AS
declare @shelf_id varchar(10)
declare @kind_id varchar(10)--080507
declare @shelfNum INT
--declare  @dt1 datetime
declare  @js  INT
declare  @num1  INT--存放调增货物数量
declare  @n INT
SET NOCOUNT ON
--IF ISNULL(@createdate,'')='' RETURN--080506
IF ISNULL(@spec,'')='' RETURN
--IF ISNULL(@goodstype,'')='' RETURN
--IF ISNULL(@unit,'')='' RETURN
IF ISNULL(@kind,'')='' RETURN
IF ISNULL(CAST(@num as DECIMAL),0)

CREATE TABLE #temp(
SHELFID varchar(10) NOT NULL,
NUMBER int  NULL
)
set @kind_id=(select KIND_ID  FROM KIND WHERE [url=mailto:KIND_NAME=@kind]KIND_NAME=@kind[/url] )

--///////////////////////////////////////////////////
SELECT DISTINCT FLOOR,WAREHOUSE,AREA,CHANNEL,DEEP,shelf_id,AMOUNT into #t1 FROM CURRENT_SHELF_STORAGE_VIEW WHERE  [url=mailto:SPEC_ID=@spec]SPEC_ID=@spec[/url] AND AMOUNT

BEGIN---------------------------------------LOOP1
--调增
if cast(CAST(@num as DECIMAL) as int)
else
BEGIN---------------------------------------LOOP2
set @shelf_id=(select shelf_id  FROM #t1)
set @num1=@js-(select AMOUNT  FROM #t1)
INSERT #temp VALUES(@shelf_id,@num1)
set @num =cast(CAST(@num as DECIMAL) as int)-@num1
IF(CAST(CAST(@num as DECIMAL) AS INT)%@js =0)
BEGIN
  SET @shelfNum=cast(CAST(@num as DECIMAL) as int)/@js
END
ELSE
BEGIN
SET @shelfNum=cast(CAST(@num as DECIMAL) as int)/@js+1
END
--变量@shelfNum为当前剩余货物分配货位数量
set @n=@shelfNum+1
--set ROWCOUNT  @shelfNum  DECLARE c CURSOR LOCAL FOR
set ROWCOUNT  @n DECLARE c CURSOR LOCAL FOR
select shelf_view.shelf_id from  shelf_view WHERE shelf_view.POS_STATUS = '0'and [url=mailto:shelf_view.KIND_ID=@kind_id--080507]shelf_view.KIND_ID=@kind_id--080507[/url]
OPEN c
FETCH    c into @shelf_id
WHILE @@FETCH_STATUS = 0
--fetch语句成功（结果集中存在行）
  BEGIN
  IF(cast(CAST(@num as DECIMAL) as int)>@js)
  BEGIN
  INSERT #temp VALUES(@shelf_id,@js)
END
ELSE
  BEGIN
INSERT #temp VALUES(@shelf_id,cast(CAST(@num as DECIMAL) as int))
END
FETCH  NEXT FROM  c  into @shelf_id
set @num =cast(CAST(@num as DECIMAL) as int)-@js
set @shelfNum=@shelfNum-1
set @n=@n-1
END
  CLOSE c
  DEALLOCATE c
--if(@shelfNum0)
if(@n0)
--如果同排同列空货位不够，即没有分配完，则在剩下空货位中按顺序分配地址
BEGIN
--set ROWCOUNT @shelfNum  DECLARE shlef_cursor CURSOR LOCAL FOR SELECT b.shelf_id FROM
set ROWCOUNT @n  DECLARE shlef_cursor CURSOR LOCAL FOR SELECT b.shelf_id FROM
dbo.NULL_CHANNEL_VIEW  a INNER JOIN dbo.SHELF_VIEW b ON a.FLOOR = b.FLOOR AND a.WAREHOUSE  =b.WAREHOUSE AND a.AREA = b.AREA AND a.CHANNEL = b.CHANNEL
open shlef_cursor
FETCH shlef_cursor into @shelf_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF(cast(CAST(@num as DECIMAL) as int)>@js)
  BEGIN
  INSERT #temp VALUES(@shelf_id,@js)
END
ELSE
  BEGIN
INSERT #temp VALUES(@shelf_id,cast(CAST(@num as DECIMAL) as int))
END
  FETCH NEXT FROM shlef_cursor into @shelf_id
set @num =cast(CAST(@num as DECIMAL) as int)-@js
set @shelfNum=@shelfNum-1
END
  CLOSE shlef_cursor
  DEALLOCATE shlef_cursor
END
END-----------------------------------------LOOP2
END-----------------------------------------LOOP1
else
BEGIN---------------------------------------LOOP1
set ROWCOUNT  @shelfNum  DECLARE c CURSOR LOCAL FOR
select shelf_view.shelf_id from  shelf_view WHERE shelf_view.POS_STATUS = '0'and [url=mailto:shelf_view.KIND_ID=@kind_id--080507]shelf_view.KIND_ID=@kind_id--080507[/url]
OPEN c
FETCH    c into @shelf_id
WHILE @@FETCH_STATUS = 0
--fetch语句成功（结果集中存在行）
  BEGIN
  IF(cast(CAST(@num as DECIMAL) as int)>@js)
  BEGIN
  INSERT #temp VALUES(@shelf_id,@js)
END
ELSE
  BEGIN
INSERT #temp VALUES(@shelf_id,cast(CAST(@num as DECIMAL) as int))
END
FETCH  NEXT FROM  c  into @shelf_id
set @num =cast(CAST(@num as DECIMAL) as int)-@js
set @shelfNum=@shelfNum-1
END
  CLOSE c
  DEALLOCATE c
if(@shelfNum0)
--如果同排同列空货位不够，即没有分配完，则在剩下空货位中按顺序分配地址
BEGIN
set ROWCOUNT @shelfNum  DECLARE shlef_cursor CURSOR LOCAL FOR SELECT b.shelf_id FROM
dbo.NULL_CHANNEL_VIEW  a INNER JOIN dbo.SHELF_VIEW b ON a.FLOOR = b.FLOOR AND a.WAREHOUSE  =b.WAREHOUSE AND a.AREA = b.AREA AND a.CHANNEL = b.CHANNEL
open shlef_cursor
FETCH shlef_cursor into @shelf_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF(cast(CAST(@num as DECIMAL) as int)>10)
  BEGIN
  INSERT #temp VALUES(@shelf_id,@js)
END
ELSE
  BEGIN
INSERT #temp VALUES(@shelf_id,cast(CAST(@num as DECIMAL) as int))
END
  FETCH NEXT FROM shlef_cursor into @shelf_id
set @num =cast(CAST(@num as DECIMAL) as int)-@js
set @shelfNum=@shelfNum-1
END
  CLOSE shlef_cursor
  DEALLOCATE shlef_cursor
END
END-----------------------------------------LOOP1

select * FROM #temp
DROP table #temp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[ 本帖最后由 wiwi 于 2008-5-8 09:19 编辑 ]