SQL Server 查询时间段内数据

方式一：

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语句执行的效率。

摘自：http://tieba.baidu.com/p/2762861623