导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

查询分析器行,存储过程却执行失败

wea1978 2007-12-28 09:47:22
如下语句,放在查询分析器执行成功:

/*但两样在查询分析器进行调用时却有错误提示
Server: Msg 536, Level 16, State 3, Procedure P_ViewPro, Line 29
Invalid length parameter passed to the substring function.

调用语句 exec P_ViewPro '2007-12-01','2007-12-27'

dbo.V_ProViewCore 是个嵌套视图,如果清除 and (V_WorkSheetView.StarDate between @st and @en)
则在任何地方执行成功.
*/

/*存储过程P_ViewPro*/
declare @st datetime,@en datetime

SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (V_WorkSheetView.StarDate between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per

...全文
268 点赞 收藏 22
写回复
22 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wea1978 2007-12-28
感谢大家的帮助!谢谢!
祝各位元日愉快!
回复
wea1978 2007-12-28
Yes!
问题找到了...是 dbo.V_ProViewCore 视图出的问题...
在视图内的where 后加入 isnull(per,'')<>'' and,执行成功!

select product,'0.1~0.21' as Dept,sty,StyID,per from products
where
isnull(per,'')<>'' and charindex('G',Per)>0 and cast((replace(Per,'G','.')) as numeric(18,3))<0.22
union all
select product,'0.22~0.99',sty,StyID,per from products
where
isnull(per,'')<>'' and charindex('G',Per)>0
and cast((replace(Per,'G','.')) as numeric(18,3))>=0.22
and cast((replace(Per,'G','.')) as numeric(18,3))<=0.99
union all
select product,'1G~9.9G',sty,StyID from products
where
isnull(per,'')<>'' and charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))<10
and (left(Per,charindex('G',Per)-1))>=1
union all
select product,'10G~100K',sty,StyID,per from products
where Per like '%K%'
union all
select product,'10G~100K',sty,StyID,per from products
where
isnull(per,'')<>'' and
charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))>=10
union all
select product,'100KG',sty,StyID,per from products
where Per like'%KG%'
回复
wea1978 2007-12-28
因为select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en
执行成功,我把这个数据结果存入另外一个表a,然后将我开始贴出来的V_WorkSheetView换成新表a,
最后不使用 (V_WorkSheetView.StarDate between @st and @en)
唉...结果还是错....
意思就是说,出错的地方,并不在此句....
回复
wea1978 2007-12-28
谢谢各位的热情帮助...
数据没有代表性,几十万条,不知道到底是哪条出了问题...
回复
-狙击手- 2007-12-28
你搞点数据上来,大家帮你测试一下
回复
wea1978 2007-12-28
使用 isnull(V_WorkSheetView.StarDate,'1900-01-01') ,这个方法试过的.不行
也试过不用between ,用>= and <= 这样,还有,<>'',这样同样报错
回复
-狙击手- 2007-12-28
create   procedure   P_ViewPro   @st   datetime,@en   datetime   
as
SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (isnull(V_WorkSheetView.StarDate,'1900-01-01') between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per
go
回复
wea1978 2007-12-28
奇怪...怎么发了2次呢...晕
回复
wea1978 2007-12-28
不会报错...我开始贴出来的语句在查询分析器都是可以执行的...
只不过放到存储过程,使用exec P_ViewPro '2007-12-01','2007-12-27' 却失败了...

将.

select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en

放到存储过程,只执行这条语句..是成功的.
回复
wea1978 2007-12-28
不会报错...我开始贴出来的语句在查询分析器都是可以执行的...
只不过放到存储过程,使用exec P_ViewPro '2007-12-01','2007-12-27' 却失败了...

将.

select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en

放到存储过程,只执行这条语句..是成功的.
回复
-狙击手- 2007-12-28
select * from V_ProViewCore where V_WorkSheetView.StarDate between @st and @en
回复
-狙击手- 2007-12-28

select * from V_ProViewCore
不报错吗????
回复
wea1978 2007-12-28
dbo.V_ProViewCore有用到...不过却是执行成功的,V_WorkSheetView没有用到...也可以换成表,也是同样错误...
以下是dbo.V_ProViewCore

select product,'0.1~0.21' as Dept,sty,StyID,per from products
where charindex('G',Per)>0 and cast((replace(Per,'G','.')) as numeric(18,3))<0.22
union all
select product,'0.22~0.99',sty,StyID,per from products
where charindex('G',Per)>0
and cast((replace(Per,'G','.')) as numeric(18,3))>=0.22
and cast((replace(Per,'G','.')) as numeric(18,3))<=0.99
union all
select product,'1G~9.9G',sty,StyID from products
where charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))<10
and (left(Per,charindex('G',Per)-1))>=1
union all
select product,'10G~100K',sty,StyID,per from products
where Per like '%K%'
union all
select product,'10G~100K',sty,StyID,per from products
where
charindex('G',Per)>0 and left(Per,charindex('G',Per)-1)<>''
and isnumeric(left(Per,charindex('G',Per)-1))>0
and (left(Per,charindex('G',Per)-1))>=10
union all
select product,'100KG',sty,StyID,per from products
where Per like'%KG%'
回复
-狙击手- 2007-12-28
V_WorkSheetView
这个里面有用substring函数吗
回复
wea1978 2007-12-28
呵呵...不会使用CSDN插入源代码功能,见笑了...我说我贴出来的怎么不同呢...
回复
wea1978 2007-12-28
create procedure P_ViewPro @st datetime,@en datetime
as
SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (V_WorkSheetView.StarDate between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per
go

exec P_ViewPro '2007-12-01','2007-12-27'

/*
呵呵,我的存储过程当然是这样写的啊.
我没有写create procedure P_ViewPro @st datetime,@en datetime
as

是因为我已经说明了是存储过程.只是把语句写出来而已...

Haiwer 朋友的分析很有可能...问题是出在 V_WorkSheetView.StarDate between @st and @en
因为 不使用这个语句就可以执行...不过这是关键语句,不能去除...出错的原因可能是这个datetime字段饱含有Null值.
但使用去除null的语句也不管用...
奇怪了...


*/
回复
rouqu 2007-12-28
--Invalid length parameter passed to the substring function

-----
根据错误提示 sp里有substring函数 而且里面的长度参数无效 (SUBSTRING(expression , start , length))

回复
-狙击手- 2007-12-28
应该是视图吧
回复
dawugui 2007-12-28
create procedure P_ViewPro @stdatetime,@en datetime 
as
SELECT V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept, dbo.V_ProViewCore.sty,
dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per,
SUM(ISNULL(V_WorkSheetView.Anum, 0) + ISNULL(V_WorkSheetView.Bnum, 0)
+ ISNULL(V_WorkSheetView.Cnum, 0)) AS InNum,
SUM(ISNULL(WorkSheet_1.Anum, 0) + ISNULL(WorkSheet_1.Bnum, 0)
+ ISNULL(WorkSheet_1.Cnum, 0)) AS OutNum
FROM dbo.MakeItems INNER JOIN
V_WorkSheetView ON dbo.MakeItems.[Lot No] = V_WorkSheetView.[Lot No] INNER JOIN
dbo.V_ProViewCore ON
dbo.MakeItems.product = dbo.V_ProViewCore.product INNER JOIN
V_WorkSheetView WorkSheet_1 ON
dbo.MakeItems.[Lot No] = WorkSheet_1.[Lot No]
WHERE (V_WorkSheetView.orderid = '1') AND (WorkSheet_1.orderid = '6')
and (V_WorkSheetView.StarDate between @st and @en)
GROUP BY V_WorkSheetView.[Lot No],dbo.V_ProViewCore.product, dbo.V_ProViewCore.Dept,
dbo.V_ProViewCore.sty, dbo.V_ProViewCore.StyID, dbo.V_ProViewCore.Per
go

exec P_ViewPro '2007-12-01','2007-12-27'
回复
wzy_love_sly 2007-12-28
不是过程的错吧?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告