不用表变量和临时表 create proc pr_test @a varchar(20) asselect * frin test where a=@a --这个返回前端的就是记录集 go
Create Procedure p_name @v varchar(10) as beginselect * from tb where name=@vend
如果没有什么业务逻辑,还不如直接写sql
ALTER PROCEDURE [dbo].[P_CW_GetTSInfo] @ATopCount int = 50, --控制行数 @AFCCK varchar(50)='',--被投诉仓库 @AFCBM varchar(50)='',--被投诉部门 @AFCDD varchar(50)='',--被投诉地点 @AFBTSYG varchar(50)='',--被投诉员工 @AFTSCS int=0,--投诉次数 @AFTSCLCS int=0,--投诉成立次数 @AFMoney money=0,--处罚金额 @AFLSMoney money=0,--流失金额 @ASDate varchar(50)='',--开始时间 @AEDate varchar(50)='',--结束时间 @AWhereStr varchar(1000) = '1=1' --权限控制 AS BEGIN SET NOCOUNT ON; Declare @WhereStr varchar(1000) --内层条件 Declare @WCWhereStr varchar(1000) --外层条件 Declare @SBDate varchar(50) Declare @EBDate varchar(50) Set @SBDate = @ASDate Set @EBDate = @AEDate Set @WhereStr = '' Set @WCWhereStr = '' if @AFCCK <> '' Set @WhereStr = @WhereStr + ' and A.FC26 = '''+@AFCCK+'''' if @AFBTSYG <>'' Set @WhereStr = @WhereStr + ' and B.FCYG = '''+@AFBTSYG+''' ' Set @WCWhereStr = ' and FTSCLCS >= '+cast(@AFTSCLCS as varchar(20))+' and FTSCS >= '+cast(@AFTSCS as varchar(20))+'' if @AFCDD <> '' Set @WCWhereStr = @WCWhereStr + ' and FCDD like '''+@AFCDD+'%'+''' ' if @AFMoney <> 0 Set @WCWhereStr = @WCWhereStr + ' and FMoney >= '+cast(@AFMoney as varchar(20))+' ' if @AFLSMoney <> 0 Set @WCWhereStr = @WCWhereStr + ' and FLSMoney >= '+cast(@AFLSMoney as varchar(20))+' ' if @AFCBM <> '' Set @WCWhereStr = @WCWhereStr + ' and FCBM like '''+@AFCBM+'%'+'''' Set @ASDate = (case when @ASDate='' then '1900-01-01 00:00:00' else @ASDate end ) Set @AEDate = (case when @AEDate='' then Substring(CONVERT(VARCHAR, getdate()+1, 120),1,10) else @AEDate end ) EXEC('Select Top '+@ATopCount+' * from (Select FC26 as FCCK,FHCBM as FCBM,FBTSYG as FBTSYG ,Fname as FCDD,sum(isnull(FB9,0))+ sum(isnull(Fb11,0)) as FMoney,Sum(isnull(FB7,0)) as FLSMoney,isnull(FTSCS,0) as FTSCS ,isnull(FTSCLCS,0) as FTSCLCS,case when (datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26 = C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end))) = 0 then isnull(FTSCLCS,0)/1.0 else isnull(FTSCLCS,0)/((datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end)))*1.0) end as FAvgCS,FLastTime from (Select A.FC26,MM.FDeptID as FHCBM,B.Fname,B.FCYG as FBTSYG,A.FB9,A.FB11,A.FB7,E.FTSCS,D.FTSCLCS,F.FLastTime from D_CastTSD A left outer join A_Stock B on A.FC26 = B.FNumber left outer join S_person MM on B.FCYG = MM.FID left outer join (select FC26,count(*) as FTSCLCS from D_CastTSD where FCHT=''物流类投诉'' and Fb10=1 and FAuditPoint=0 and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) D on A.fc26 = D.fc26 --投诉成立次数 left outer join (select FC26,count(*)as FTSCS from D_CastTSD where FCHT=''物流类投诉'' and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) E on A.fc26 = E.fc26 --投诉次数 left outer join (select FC26,max(FInputTime) as FLastTime from D_CastTSD where FCHT=''物流类投诉'' group by FC26) F on A.FC26 = F.FC26 --得到最后一次投诉时间 where (A.FCHT=''物流类投诉'') and (A.Finputtime between '''+@ASDate+''' and '''+@AEDate+''')'+@WhereStr+' ) C group by FC26,FHCBM,Fname,FBTSYG,FLastTime,FTSCS,FTSCLCS) T where 1=1 '+@WCWhereStr+' and '+@AWhereStr+'') --print --'Select Top '+cast(@ATopCount as varchar(50))+' * from (Select FC26 as FCCK,FHCBM as FCBM,FBTSYG as FBTSYG ,Fname as FCDD,sum(isnull(FB9,0))+ sum(isnull(Fb11,0)) as FMoney,Sum(isnull(FB7,0)) as FLSMoney,isnull(FTSCS,0) as FTSCS ,isnull(FTSCLCS,0) as FTSCLCS,case when (datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26 = C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end))) = 0 then isnull(FTSCLCS,0)/1.0 else isnull(FTSCLCS,0)/((datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end)))*1.0) end as FAvgCS,FLastTime from -- (Select A.FC26,MM.FDeptID as FHCBM,B.Fname,B.FCYG as FBTSYG,A.FB9,A.FB11,A.FB7,E.FTSCS,D.FTSCLCS,F.FLastTime from D_CastTSD A -- left outer join A_Stock B on A.FC26 = B.FNumber -- left outer join S_person MM on B.FCYG = MM.FID -- left outer join -- (select FC26,count(*) as FTSCLCS from D_CastTSD where FCHT=''物流类投诉'' and Fb10=1 and FAuditPoint=0 and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) D on A.fc26 = D.fc26 --投诉成立次数 -- left outer join -- (select FC26,count(*)as FTSCS from D_CastTSD where FCHT=''物流类投诉'' and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) E on A.fc26 = E.fc26 --投诉次数 -- left outer join -- (select FC26,max(FInputTime) as FLastTime from D_CastTSD where FCHT=''物流类投诉'' group by FC26) F on A.FC26 = F.FC26 --得到最后一次投诉时间 -- where (A.FCHT=''物流类投诉'') and (A.Finputtime between '''+@ASDate+''' and '''+@AEDate+''')'+@WhereStr+' ) C -- group by FC26,FHCBM,Fname,FBTSYG,FLastTime,FTSCS,FTSCLCS) T where 1=1 '+@WCWhereStr+' and '+@AWhereStr+'' SET NOCOUNT OFF; END给你个例子
create proc pr_test
@a varchar(20)
asselect * frin test where a=@a --这个返回前端的就是记录集
go
Create Procedure p_name
@v varchar(10)
as
beginselect * from tb
where name=@vend
如果没有什么业务逻辑,还不如直接写sql
@ATopCount int = 50, --控制行数
@AFCCK varchar(50)='',--被投诉仓库
@AFCBM varchar(50)='',--被投诉部门
@AFCDD varchar(50)='',--被投诉地点
@AFBTSYG varchar(50)='',--被投诉员工
@AFTSCS int=0,--投诉次数
@AFTSCLCS int=0,--投诉成立次数
@AFMoney money=0,--处罚金额
@AFLSMoney money=0,--流失金额
@ASDate varchar(50)='',--开始时间
@AEDate varchar(50)='',--结束时间
@AWhereStr varchar(1000) = '1=1' --权限控制
AS
BEGIN
SET NOCOUNT ON;
Declare @WhereStr varchar(1000) --内层条件
Declare @WCWhereStr varchar(1000) --外层条件
Declare @SBDate varchar(50)
Declare @EBDate varchar(50)
Set @SBDate = @ASDate
Set @EBDate = @AEDate
Set @WhereStr = ''
Set @WCWhereStr = ''
if @AFCCK <> ''
Set @WhereStr = @WhereStr + ' and A.FC26 = '''+@AFCCK+''''
if @AFBTSYG <>''
Set @WhereStr = @WhereStr + ' and B.FCYG = '''+@AFBTSYG+''' '
Set @WCWhereStr = ' and FTSCLCS >= '+cast(@AFTSCLCS as varchar(20))+' and FTSCS >= '+cast(@AFTSCS as varchar(20))+''
if @AFCDD <> ''
Set @WCWhereStr = @WCWhereStr + ' and FCDD like '''+@AFCDD+'%'+''' '
if @AFMoney <> 0
Set @WCWhereStr = @WCWhereStr + ' and FMoney >= '+cast(@AFMoney as varchar(20))+' '
if @AFLSMoney <> 0
Set @WCWhereStr = @WCWhereStr + ' and FLSMoney >= '+cast(@AFLSMoney as varchar(20))+' '
if @AFCBM <> ''
Set @WCWhereStr = @WCWhereStr + ' and FCBM like '''+@AFCBM+'%'+''''
Set @ASDate = (case when @ASDate='' then '1900-01-01 00:00:00' else @ASDate end )
Set @AEDate = (case when @AEDate='' then Substring(CONVERT(VARCHAR, getdate()+1, 120),1,10) else @AEDate end )
EXEC('Select Top '+@ATopCount+' * from (Select FC26 as FCCK,FHCBM as FCBM,FBTSYG as FBTSYG ,Fname as FCDD,sum(isnull(FB9,0))+ sum(isnull(Fb11,0)) as FMoney,Sum(isnull(FB7,0)) as FLSMoney,isnull(FTSCS,0) as FTSCS ,isnull(FTSCLCS,0) as FTSCLCS,case when (datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26 = C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end))) = 0 then isnull(FTSCLCS,0)/1.0 else isnull(FTSCLCS,0)/((datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end)))*1.0) end as FAvgCS,FLastTime from
(Select A.FC26,MM.FDeptID as FHCBM,B.Fname,B.FCYG as FBTSYG,A.FB9,A.FB11,A.FB7,E.FTSCS,D.FTSCLCS,F.FLastTime from D_CastTSD A
left outer join A_Stock B on A.FC26 = B.FNumber
left outer join S_person MM on B.FCYG = MM.FID
left outer join
(select FC26,count(*) as FTSCLCS from D_CastTSD where FCHT=''物流类投诉'' and Fb10=1 and FAuditPoint=0 and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) D on A.fc26 = D.fc26 --投诉成立次数
left outer join
(select FC26,count(*)as FTSCS from D_CastTSD where FCHT=''物流类投诉'' and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) E on A.fc26 = E.fc26 --投诉次数
left outer join
(select FC26,max(FInputTime) as FLastTime from D_CastTSD where FCHT=''物流类投诉'' group by FC26) F on A.FC26 = F.FC26 --得到最后一次投诉时间
where (A.FCHT=''物流类投诉'') and (A.Finputtime between '''+@ASDate+''' and '''+@AEDate+''')'+@WhereStr+' ) C
group by FC26,FHCBM,Fname,FBTSYG,FLastTime,FTSCS,FTSCLCS) T where 1=1 '+@WCWhereStr+' and '+@AWhereStr+'')
--print
--'Select Top '+cast(@ATopCount as varchar(50))+' * from (Select FC26 as FCCK,FHCBM as FCBM,FBTSYG as FBTSYG ,Fname as FCDD,sum(isnull(FB9,0))+ sum(isnull(Fb11,0)) as FMoney,Sum(isnull(FB7,0)) as FLSMoney,isnull(FTSCS,0) as FTSCS ,isnull(FTSCLCS,0) as FTSCLCS,case when (datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26 = C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end))) = 0 then isnull(FTSCLCS,0)/1.0 else isnull(FTSCLCS,0)/((datediff(week,(case when '''+@SBDate+'''='''' then (Select min(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@SBDate+''' end),(case when '''+@EBDate+'''='''' then (Select max(Finputtime) from D_CastTSD where FC26=C.FC26) else '''+@EBDate+''' end)))*1.0) end as FAvgCS,FLastTime from
-- (Select A.FC26,MM.FDeptID as FHCBM,B.Fname,B.FCYG as FBTSYG,A.FB9,A.FB11,A.FB7,E.FTSCS,D.FTSCLCS,F.FLastTime from D_CastTSD A
-- left outer join A_Stock B on A.FC26 = B.FNumber
-- left outer join S_person MM on B.FCYG = MM.FID
-- left outer join
-- (select FC26,count(*) as FTSCLCS from D_CastTSD where FCHT=''物流类投诉'' and Fb10=1 and FAuditPoint=0 and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) D on A.fc26 = D.fc26 --投诉成立次数
-- left outer join
-- (select FC26,count(*)as FTSCS from D_CastTSD where FCHT=''物流类投诉'' and Finputtime between '''+@ASDate+''' and '''+@AEDate+''' group by fc26) E on A.fc26 = E.fc26 --投诉次数
-- left outer join
-- (select FC26,max(FInputTime) as FLastTime from D_CastTSD where FCHT=''物流类投诉'' group by FC26) F on A.FC26 = F.FC26 --得到最后一次投诉时间
-- where (A.FCHT=''物流类投诉'') and (A.Finputtime between '''+@ASDate+''' and '''+@AEDate+''')'+@WhereStr+' ) C
-- group by FC26,FHCBM,Fname,FBTSYG,FLastTime,FTSCS,FTSCLCS) T where 1=1 '+@WCWhereStr+' and '+@AWhereStr+''
SET NOCOUNT OFF;
END给你个例子