if Exists(select name from sysobjects where NAME = ‘GetTable‘ and type=‘P‘)
drop procedure GetTable
go
CREATE PROCEDURE GetTable
@name nvarchar(50)=null,
@Time1 datetime=‘2017-03-07 00:00:00‘,
@Time2 datetime=null
AS
BEGIN
DECLARE @Str nvarchar(max)=‘‘
if @Time2 is null
BEGIN
set @Time2=GETDATE()
END
set @Str=‘SELECT w.ID,w.UserAccount,(SELECT COUNT(1) AS Expr1 FROM dbo.BI6WebSiteUser AS c WHERE (ParentAgencyID = w.ID) AND (UserType = 2)) AS Agentcount
isnull(SUM(p.Paymoney),0) as RechargeAmount,isnull(Sum(isnull(p.AgentDividedMoney,0)),0) as CommissionAmount
from dbo.WebSiteUser as w , dbo.Boing_PayHheOrderForm as p
where w.ID=p.AgentDividedID and p.IsDelete=1 and p.CreateTime>=‘+@Time1+‘ and p.CreateTime<=‘+@Time2
if @name is not null
BEGIN
set @Str+=‘ and UserAccount=‘+@name
END
set @Str+=‘ GROUP BY w.ID,w.UserAccount‘
Return @Str
END
--declare @SrtDate nvarchar(max)
execute GetTable NULL,‘2017-12-05 17:35:27.193‘
--print @SrtDate @SrtDate=