方式一：
ALTER Proc [dbo].[usp_Rpt_AcctTypeAudit]
@FromDate datetime=null, -- yyyy-mm-dd (may change in the future!)
@ToDate datetime=null, -- yyyy-mm-dd (may change in the future!)
@UserID nvarchar(50) = 'ALL'
as
BEGIN
set nocount on
if @ToDate is not null
begin
set @ToDate=convert(varchar,@ToDate,112)+' 23:59:59:998'
end select
AcctType,AcctDesc,HostAcctType,AcctNumLength,
case [Action] when 'A' then 'Add' when 'D' then 'Delete' when 'M' then 'Before-Modify' when 'N' then 'After-Modify' else '' end as [Action]
,UserID,WsID,CrtTime
from AcctTypeMasterHist with(nolock)
where (CrtTime >= @FromDate or @FromDate is null)
and (CrtTime <= @ToDate or @ToDate is null)
and (UserID = @UserID or @UserID='ALL' or isnull(@UserID,'')='')
order by crttime,LogID
END
方式二：
create PROCEDURE [dbo].[Sp_CCBA_ProcessLogRpt]
@FromDate datetime,
@ToDate datetime,
@UserID varchar(27),
@Workstation varchar(28)
AS Select * from ProcessLogInf
WHERE DATEDIFF(day, @FromDate, AcDate)>= 0
ANd DATEDIFF(day, AcDate, @ToDate)>= 0
AND LogUser = CASE RTRIM(@UserID) WHEN 'ALL' THEN LogUser WHEN '' THEN LogUser ELSE @UserID END
AND LogWs = CASE RTRIM(@Workstation) WHEN 'ALL' THEN LogWs WHEN '' THEN LogWs ELSE @Workstation END
效率分析：
作为SqlServer内置函数存在的DateDiff，在执行的过程中需要再解释，跟使用“>”和“<”逻辑关系拼结在语句相比，在执行效率上很大打折扣。
由此可以看出来内置函数DateDiff果然在执行的时候进行了再分析。
那么在以后的使用过程中如果可以用“>”和“<”号这种逻辑关系所能拼结并能够正确得到结果的使用，可以适当的抛弃使用DateDiff这种SqlServer内置的函数，会相应的提高编写的Sql语句执行的效率。