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

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

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

...全文

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+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告