/*
函数功能:查询并且返回当前的审核状态
返回值 : tinyint
-1 通过审核
1 未通过审核
0 未审核
*/
CREAET FUNCTION fn_GetStatus(@ID int)
RETURNS TINGINT
AS
BEGIN
DECLARE @Status TINYINT
DECLARE @Count INT
--查找状态的的值
SELECT @Status=SUM(
CASE checkstadeid
WHEN '审核拒绝' TEHN 0
WHEN '审核通过' THEN 1
ELSE -1000 --此时的赋值,可以是 X
END )
FROM indent WHERE 主键=@ID
SELECT @COUNT=COUNT(*) FROM indent WHERE 主键=@ID
--判断状态的,并进行设置
IF @Status=@Count --全部通过
@Status=-1
ELSE
@Status=1
create function checkstatename(@id varchar(1000))
returns varchar(100) as
begin
declare @flag varchar(100)
select * from 状态表 where checkstadeid=@id and 审核状态='通过'
if @@rowcount>0
select @flag='通过'
else
select @flag='未通过'
return @flag
end
create function checkstatename (@checkstateid varchar(10))
returns bit
as
begin
if exists(select * from 审核状态表 where checkstateid =@checkstateid
and 状态 in ('审核拒绝','未审核') )
Return(0)
Else
Return(1)
end
Go
create function 得到状态(@订单号 int)
returns varchar(10)
as
begin
declare @a varchar(10)
if not exists(select 1 from 审核状态表 where 订单号=@订单号 and 状态 in ('拒绝','未审核'))
set @a='审核通过'
else
set @a='未审核通过'
return @a
end
create function checkstatename (@checkstateid varchar(10))
returns bit
as
begin
if exists(select * from 审核状态表 where checkstateid =@checkstateid
and 状态 in ('审核拒绝','未审核') )
Return(0)
Else
Return(1)
end
Go