如下语句,放在查询分析器执行成功:/*但两样在查询分析器进行调用时却有错误提示
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 datetimeSELECT 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
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 datetimeSELECT 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
--问题不在存储过程中
--找一下你的视图代码(29行附近),有没有使用substring,left,right,charindex等函数,
--这里的参数在某些数据条件下会出错,应该增加判断来排除错误
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
goexec P_ViewPro '2007-12-01','2007-12-27'
-----
根据错误提示 sp里有substring函数 而且里面的长度参数无效 (SUBSTRING(expression , start , length))
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
goexec 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的语句也不管用...
奇怪了...
*/
这个里面有用substring函数吗
以下是dbo.V_ProViewCoreselect 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%'
select * from V_ProViewCore
不报错吗????
只不过放到存储过程,使用exec P_ViewPro '2007-12-01','2007-12-27' 却失败了...将.
select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en 放到存储过程,只执行这条语句..是成功的.
只不过放到存储过程,使用exec P_ViewPro '2007-12-01','2007-12-27' 却失败了...将.
select * from V_WorkSheetView where V_WorkSheetView.StarDate between @st and @en 放到存储过程,只执行这条语句..是成功的.
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
也试过不用between ,用>= and <= 这样,还有,<>'',这样同样报错
执行成功,我把这个数据结果存入另外一个表a,然后将我开始贴出来的V_WorkSheetView换成新表a,
最后不使用 (V_WorkSheetView.StarDate between @st and @en)
唉...结果还是错....
意思就是说,出错的地方,并不在此句....
问题找到了...是 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%'