sql server语言 - sql:搜索空日期

Question

我有一个搜索Members表的选择过程。Members表有:

ID – Name –  Deleted
 1 – Marcus – NULL
 2 – Regina – 2014-03-26 09:33:00.000

正在:ID一个PK int NULL，命名一个varchar(250)，删除一个datetime NULL。

步骤如下:

CREATE PROCEDURE dbo.SELECT_MEMBERS
    @ID int = NULL,
    @Name varchar(250)= NULL,
    @Deleted datetime =NULL
AS
BEGIN
 IF @Deleted = ''
        BEGIN
            SET @Deleted = NULL
        END     
        ELSE 
        BEGIN
            SET @Deleted = @Deleted
        END
 SET NOCOUNT ON;
    SELECT  ID, Name, Deleted 
    FROM dbo.Members WHERE
       (@ID IS NULL OR @ID = ID)
   AND (@Name IS NULL OR @Name = Name)
   AND (@Deleted IS NULL OR @Deleted = Deleted)

END
GO 

为了搜索没有删除日期的成员(如果他们有删除日期意味着他们不是活动成员)，我这样做:

EXEC dbo.SELECT_MEMBERS  @Deleted = ''

但是这不起作用，因为我插入了删除日期的行。

我做错了什么?

非常感谢!

Answer 1

CREATE PROCEDURE dbo.SELECT_MEMBERS
    @ID int = NULL,
    @Name varchar(250)= NULL,
    @Deleted datetime =NULL
AS
BEGIN
 SET NOCOUNT ON;
    SELECT  ID, Name, Deleted 
    FROM dbo.Members 
    WHERE ID = ID
    AND ISNULL(@Name, '') = ISNULL(Name, '')
    AND ISNULL(@Deleted, '') = ISNULL(Deleted, '')

END
GO 

Answer 2

似乎您希望@Deleted日期的空/NULL值与@ID和@Name的NULL值的行为不同:您希望@Deleted的实际比较仅在值为NULL时为真。

我想你想要这样的东西:

CREATE PROCEDURE dbo.SELECT_MEMBERS
    @ID int = NULL,
    @Name varchar(250)= NULL,
    @Deleted datetime = NULL
AS
BEGIN
   SELECT ID, Name, Deleted 
   FROM dbo.Members
   WHERE (@ID IS NULL OR @ID = ID) AND
         (@Name IS NULL OR @Name = Name) AND
         ((@Deleted IS NULL  OR @Deleted = Deleted or @Deleted = '' and Deleted is null)
END;