日期:2014-05-17 浏览次数:20519 次
--创建存储过程 Alter PROC ICPM_Expeceted ( @FromTime smalldatetime, @ToTime smalldatetime, @userId int ='', @LesseeTypeId int ='', @SortField Nvarchar(200), @SortBy Nvarchar(20) ) AS --如果存在临时表就删除临时表 IF EXISTS(SELECT [Name] FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb..#tblExpecetdScoreTemp')) DROP TABLE #tblExpecetdScoreTemp SELECT RowId = IDENTITY(int,1,1), T.CustomerId, U.UserName, C.CustomerName, T.IntentScore, T.ContractScore, T.RentScore, T.StoreTypeScore, T.LesseeTypeScore, T.TotalScore INTO #tblExpecetdScoreTemp FROM ( SELECT UserId, CustomerId,IntentScore,ContractScore,RentScore,StoreTypeScore,LesseeTypeScore,TotalScore FROM tblExpecetdScore WHERE ( (@userId <> '' and userId = @userId) OR (@userId = '') ) AND ( (@LesseeTypeId <> '' and LesseeType = @LesseeTypeId) OR (@LesseeTypeId = '') ) AND ( (@FromTime = '' and @ToTime = '') OR ( contractDate between @FromTime and @ToTime ) --是在这里写的吧,请补充一下 ) )T join tblUser as U on T.UserId = U.UserId join tblCustomer as C on T.CustomerId = C.CustomerId Group by U.UserName, T.UserId, T.CustomerId, C.CustomerName, T.IntentScore, T.ContractScore, T.RentScore, T.StoreTypeScore, T.LesseeTypeScore, T.TotalScore Order by T.TotalScore ASC
Alter PROC ICPM_Expeceted ( @FromTime smalldatetime, @ToTime smalldatetime, @userId int ='', @LesseeTypeId int ='', @SortField Nvarchar(200), @SortBy Nvarchar(20) ) AS --如果存在临时表就删除临时表 IF EXISTS(SELECT [Name] FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb..#tblExpecetdScoreTemp')) DROP TABLE #tblExpecetdScoreTemp DECLARE @sql VARCHAR(8000), @where VARCHAR(2000) SET @sql='SELECT RowId = IDENTITY(int,1,1), T.CustomerId, U.UserName, C.CustomerName, T.IntentScore, T.ContractScore, T.RentScore, T.StoreTypeScore, T.LesseeTypeScore, T.TotalScore INTO #tblExpecetdScoreTemp FROM ( SELECT UserId, CustomerId,IntentScore,ContractScore,RentScore,StoreTypeScore,LesseeTypeScore,TotalScore FROM tblExpecetdScore ' IF (@FromTime='' AND @ToTime<>'') BEGIN SET @where=' WHERE 日期字段<='+CONVERT(VARCHAR(10),@FromTime,120) END IF (@ToTime='' AND @FromTime<>'') BEGIN SET @where=' WHERE 日期字段>='+CONVERT(VARCHAR(10),@ToTime,120) END IF (@FromTime<>'' AND @ToTime<>'') BEGIN SET @where=' WHERE 日期字段 between '+CONVERT(VARCHAR(10),@FromTime,120)+' and '+CONVERT(VARCHAR(10),@ToTime,120) END SET @sql=@sql+@where SET @sql+') )T join tblUser as U on T.UserId = U.UserId join tblCustomer as C on T.CustomerId = C.CustomerId Group by U.UserName, T.UserId, T.CustomerId, C.CustomerName, T.IntentScore, T.ContractScore, T.RentScore, T.StoreTypeScore, T.LesseeTypeScore, T.TotalScore Order by T.TotalScore ASC ' PRINT @sql EXEC (@sql)
------解决方案-----------------