写一个SQL的 Function 想在Store Procedures返回一个特地的字符串 大家帮我看看哪里有问题 谢谢
aDeliverySchedule 是一个表
对于每输入的一个 @StatusType 和iOrderPlanID 会有多条记录 想根据表中的iTotalCnt 和 iDeliveredCnt的关系 返回一个特地的字符串 只要返回的所有记录中有一个的 @vchStatus是 In Processing 就 返回 'In Processing'
如果所有的纪录都是Delivered 那么就返回'Delivered'
如果所有的纪录都是Scheduled 那么就返回'Scheduled'这些状态都定义在code里面 小弟总是返回0 请大家帮我看一下 谢谢
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description: get Status for Order Plan
-- =============================================
ALTER FUNCTION [dbo].[FN_OrderPlanStatus]
(
@StatusType varchar(100),
@iOrderPlanID int
)
RETURNS varchar(200)
AS
BEGINdeclare @rst varchar(200),@iTotalCnt int,@iDeliveredCnt int,int,@iDeliveryScheduleID int,@vchStatus varchar(50)
if @StatusType='Delivery'
begin
Declare aPlanStatusCursor CURSOR FOR
Select a.iScheduleID,a.iTotalCnt,a.iDeliveredCnt
from aDeliverySchedule a
where a.iOrderPlanID = @iOrderPlanID
DECLARE @aPlanStatusTable TABLE
( iDeliveryScheduleID int,
iTotalCnt int,
iDeliveredCnt int,
iOrderPlanID int,
vchStatus varchar(50)
)OPEN aPlanStatusCursor
FETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt,@iDeliveredCnt
WHILE (@@FETCH_STATUS=0)
BEGIN
if(@iTotalCnt-@iDeliveredCnt=0)
set @rst='Delivered'
else if(@iDeliveredCnt-@iTotalCnt<0 and @iDeliveredCnt<>0)
set @rst='In Processing'
else if(@iTotalCnt*@iDeliveredCnt=0)
set @rst='Scheduled'
else
set @rst='Unknown' if @rst='In Processing'
return @rst set @vchStatus=@rst
INSERT INTO @aPlanStatusTable VALUES(@iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCnt,@iOrderPlanID,@vchStatus)
FETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCnt
END
CLOSE aPlanStatusCursor
DEALLOCATE aPlanStatusCursorSelect Distinct @rst=vchStatus from @aPlanStatusTablereturn @rst
end
return @rst
END
aDeliverySchedule 是一个表
对于每输入的一个 @StatusType 和iOrderPlanID 会有多条记录 想根据表中的iTotalCnt 和 iDeliveredCnt的关系 返回一个特地的字符串 只要返回的所有记录中有一个的 @vchStatus是 In Processing 就 返回 'In Processing'
如果所有的纪录都是Delivered 那么就返回'Delivered'
如果所有的纪录都是Scheduled 那么就返回'Scheduled'这些状态都定义在code里面 小弟总是返回0 请大家帮我看一下 谢谢
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description: get Status for Order Plan
-- =============================================
ALTER FUNCTION [dbo].[FN_OrderPlanStatus]
(
@StatusType varchar(100),
@iOrderPlanID int
)
RETURNS varchar(200)
AS
BEGINdeclare @rst varchar(200),@iTotalCnt int,@iDeliveredCnt int,int,@iDeliveryScheduleID int,@vchStatus varchar(50)
if @StatusType='Delivery'
begin
Declare aPlanStatusCursor CURSOR FOR
Select a.iScheduleID,a.iTotalCnt,a.iDeliveredCnt
from aDeliverySchedule a
where a.iOrderPlanID = @iOrderPlanID
DECLARE @aPlanStatusTable TABLE
( iDeliveryScheduleID int,
iTotalCnt int,
iDeliveredCnt int,
iOrderPlanID int,
vchStatus varchar(50)
)OPEN aPlanStatusCursor
FETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt,@iDeliveredCnt
WHILE (@@FETCH_STATUS=0)
BEGIN
if(@iTotalCnt-@iDeliveredCnt=0)
set @rst='Delivered'
else if(@iDeliveredCnt-@iTotalCnt<0 and @iDeliveredCnt<>0)
set @rst='In Processing'
else if(@iTotalCnt*@iDeliveredCnt=0)
set @rst='Scheduled'
else
set @rst='Unknown' if @rst='In Processing'
return @rst set @vchStatus=@rst
INSERT INTO @aPlanStatusTable VALUES(@iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCnt,@iOrderPlanID,@vchStatus)
FETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCnt
END
CLOSE aPlanStatusCursor
DEALLOCATE aPlanStatusCursorSelect Distinct @rst=vchStatus from @aPlanStatusTablereturn @rst
end
return @rst
END
-- Description: get Status for Order Plan
-- =============================================
ALTER FUNCTION [dbo].[FN_OrderPlanStatus]
(
@StatusType varchar(100),
@iOrderPlanID int
)
RETURNS varchar(200)
AS
BEGINdeclare @rst varchar(200)if @StatusType='Delivery'
begin
select @rst=isnull(@rst+',','')+a from
(
select
case
when iTotalCnt=iDeliveredCnt then 'Delivered'
when iDeliveredCnt<iTotalCnt and iDeliveredCnt<>0 then 'In Processing'
when iTotalCnt*iDeliveredCnt=0 then 'Scheduled'
else 'Unknown'
end as a
from aDeliverySchedule where iOrderPlanID=@iOrderPlanID
) as t
group by a
endreturn(@rst)END
-- Description: get Status for Order Plan
-- =============================================
ALTER FUNCTION [dbo].[FN_OrderPlanStatus]
(
@StatusType varchar(100),
@iOrderPlanID int
)
RETURNS varchar(200)
AS
BEGINdeclare @rst varchar(200)if @StatusType='Delivery'
begin
if exists (select 1 from aDeliverySchedule where iOrderPlanID=@iOrderPlanID and iTotalCnt=iDeliveredCnt)
set @rst='Delivered'
else if exists (select 1 from aDeliverySchedule where iOrderPlanID=@iOrderPlanID and iDeliveredCnt<iTotalCnt and iDeliveredCnt<>0)
set @rst='In Processing'
else if exists (select 1 from aDeliverySchedule where iOrderPlanID=@iOrderPlanID and iTotalCnt*iDeliveredCnt=0)
set @rst='Scheduled'
else
set @rst='Unknown'
endreturn(@rst)END
Delivered
In Processing
Scheduled
Unknown
-----------
希望返回:In Processing
但如果是这几种情况呢:
Delivered
Scheduled
Unknown
-----------
你希望返回什么?
5 条 Scheduled
该返回什么?
-- Description: get Status for Order Plan
-- =============================================
ALTER FUNCTION [dbo].[FN_OrderPlanStatus]
(
@StatusType varchar(100),
@iOrderPlanID int
)
RETURNS varchar(200)
AS
BEGINdeclare @rst varchar(200)declare @temp table (iTotalCnt int, iDeliveredCnt int, vchStatus varchar(50))if @StatusType='Delivery'
begin
insert @temp select iTotalCnt,iDeliveredCnt,null from aDeliverySchedule where iOrderPlanID=@iOrderPlanID
if exists (select 1 from @temp where iDeliveredCnt<iTotalCnt and iDeliveredCnt<>0)
begin
set @rst='In Processing'
return(@rst)
end
update @temp set vchStatus=case when iTotalCnt=iDeliveredCnt then 'Delivered' when iTotalCnt*iDeliveredCnt=0 then 'Scheduled' else 'Unknown' end
if exists (select 1 from @temp where vchStatus='Unknown')
set @rst='Unknown'
else if not exists (select 1 from @temp where vchStatus<>'Delivered')
set @rst='Delivered'
else if not exists (select 1 from @temp where vchStatus<>'Scheduled')
set @rst='Scheduled'
else
set @rst='In Processing'
endreturn(@rst)END
纯粹瞎扯,哈哈