select top @TID= [plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<= @operate_Date ) t where val <= 0
...全文
605打赏收藏
怎么将以下的查询语言转换成存储过程?
怎么将以下的查询语言转换成存储过程? select top @TID= [plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<= @operate_Date ) t where val <= 0
*/
AS
Declare @TID int
select top @TID= [plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<= @operate_Date ) t where val <= 0
IF @@Rowcount < 1
SELECT @TID = ''
Else
Begin
Select * From shouliao Where [plan]=@TID
/*Delete From shouliao Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的收料单删除成功,共'+convert(char,@@Rowcount)
Delete From plant_ling Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的领料单删除成功,共'+convert(char,@@Rowcount)
Delete From main_product Where [plan]=@TID and txt<=0
select convert(char,@Operate_Date)+'之前的主仓库记录删除成功,共'+convert(char,@@Rowcount)
Delete From plant_return Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的还料单删除成功,共'+convert(char,@@Rowcount)
Delete From plant_recede where [able_plan]=@TID
select convert(char,@Operate_Date)+'之前的退料单(退车间)删除成功,共'+convert(char,@@Rowcount)
Delete from plant_rotate where able_plan=@TID
select convert(char,@Operate_Date)+'之前的转货单删除成功,共'+convert(char,@@Rowcount)
Delete From plant_product Where [plan]=@TID and txt<=0
select convert(char,@Operate_Date)+'之前的车间记录删除成功,共'+convert(char,@@Rowcount)
Delete From Main_Out Where [Able_Plan]=@TID
select convert(char,@Operate_Date)+'之前的出库单删除成功,共'+convert(char,@@Rowcount)
Delete From Main_Fade Where [Able_Plan]=@TID
select convert(char,@Operate_Date)+'之前的退料单(退客户)删除成功,共'+convert(char,@@Rowcount)
*/
End
GO
create proc test @operate_Date datetime
as
set nocount on
declare @TID numeric(12)
select top @TID= [plan]
from
(select m.* , m.plannum - isnull((select sum(Able_Amount)
from Main_Out n
where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date <= @operate_Date ) t
where val <= 0