22,209
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_cid]
(
@BOMNO varchar(100),
@liqty varchar(100)
)
returns @t_level table
(
P_CODE varchar(100),
CODE varchar(100),
CBDESC nvarchar(100),
QTY_NEED numeric(6,2),
WASTAGE numeric(6,2),
UNIT nvarchar(20),
LIQYT numeric(10,5),
level int,level1 varchar(8000)
)
as
begin
declare @level int
declare @level1 int
set @level = 1
set @level1 = '0'
insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,UNIT,
ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED)LIQYT,@level,@level1
from BOMT where BOMT.BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,(a.WASTAGE+b.WASTAGE),a.UNIT,ceiling((b.LIQYT+(b.LIQYT*(a.WASTAGE/100)))*a.QTY_NEED)LIQYT,@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1+'-000'
else b.level1+'-'+right('00'+ltrim((select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)),3)
end
from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO
end
return
end
select REPLICATE('',level-1)+ltrim(level)ID ,CODE,CBDESC,QTY_NEED,UNIT,WASTAGE,('10000'*QTY_NEED*(WASTAGE/100))SHS,LIQYT from f_cid
('WI-EGD-4740','10000') order by level1,level
select CEILING(CAST('100000' AS BIGINT)+10.0001 )
-- SET ARITHABORT
-------------------------------------------------------------------------------
-- Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
a TINYINT,
b TINYINT
);
CREATE TABLE t2 (
a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO
PRINT '*** SET ARITHABORT ON';
GO
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
GO
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be no data';
GO
SELECT *
FROM t2;
GO
-- Truncate table t2.
TRUNCATE TABLE t2;
GO
-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
GO
SET ARITHABORT OFF;
GO
-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2;
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be 0 rows';
GO
SELECT *
FROM t2;
GO
-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO