SQL server 2008 - SQL在col类型日期上使用between子句

Question

我想进行搜索查询查询asp.net web应用程序的参数

当@firstName搜索正确，但不能与between子句工作

 create proc test 
    @From Datetime,
    @to Datetime,
    @firstName varchar(50) 
    as 
    begin 
    select * from Employee 
    where Empfname=(case when @firstName != '' then @firstName else Empfname end) 
    and 1=(case when @From != null and @From != '' and @to != null and @to != '' 
    then EmpJoinDate between @From and @to else 1 end) end

Answer 1

首先，我建议您在使用"通配符"时保持一致。你的代码似乎混淆了空字符串和NULL。只要使用约定，即NULL值表示允许任何操作。

然后，从where子句中删除case，并明确列出逻辑。这里有一个方法:

create proc test (
    @From Datetime,
    @to Datetime,
    @firstName varchar(50) 
) as 
begin 
    select e.*
    from Employee e
    where (Empfname = @firstName or @firstname is null) and
          ((@from is null and @to is null) or
           (@from is null and EmpJoinDate <= @to) or
           (@to is null and EmpJoinDate >= @from) or
           (EmpJoinDate >= @from and EmpJoinDate <= @to)
          );
end;

然后，您应该重新考虑对存储过程的需求。您所拥有的实际上更适合于表值函数

Answer 2

替换基于案例的比较。而不是

Empfname=(case when @firstName != '' then @firstName else Empfname end)

写

(@firstName='' OR Empfname=@firstName)

不是

1=(case when @From != null ...

写

 (@From='' OR @To='' OR EmpJoinDate BETWEEN convert(datetime, @From)
 AND convert(datetime, @To))

注意:如果您使用NULL值来代替空字符串''，则不要对它们应用=和!=操作符，因为它们不起作用。请使用IS NULL和IS NOT NULL操作符。

Answer 3

我的语法不完全正确，因为我不知道你所针对的数据库。一般来说，我会将参数更改为非空(必需)，测试任何一个或多个空字符串，如果通过，则执行参数化SQL。

    create proc test 
    @From Datetime not null,
    @to Datetime not null,
    @firstName varchar(50) not null
    as 
    begin 
       If @From != '' or @To != '' or @FirstName != '' 
           select * from Employee 
           where Empfname= @firstName
           and  EmpJoinDate between @From and @to 
       end if
    end

Answer 4

试着修复你的程序:

-- test '20160101','20160810', 'z'
alter proc test 
    @From Datetime,
    @to Datetime,
    @firstName varchar(50)
as
DECLARE @DateCondition varchar(100),
        @FNameCondition VARCHAR(200),
        @SQLStr VARCHAR(2000)
begin 
    IF @From <> '' AND @To <> ''
        SET @DateCondition = ' EmpJoinDate between ''' + CONVERT(VARCHAR(20),@From) + ''' and ''' + CONVERT(VARCHAR(20),@To) + ''''
    IF  @firstName != '' 
        SET @FNameCondition = '''' + @firstName + ''''
    ELSE  
        SET @FNameCondition = ' Empfname '
    SET @SQLStr = '    
    select * from Employee 
    where Empfname= ' + @FNameCondition + '
    and ' + @DateCondition
    PRINT (@SQLStr)
    EXEC (@SQLStr)
END
Go