基于输入的 SQL Server 日期检查

发布于 2024-07-26 00:20:35 字数 583 浏览 4 评论 0原文

这个问题已经困扰了我很多次，我现在决定尝试找到正确的解决方案，而不是我总是恢复到（复制sql语句）的冗长/肮脏/可怕的方式

该表有一个日期列默认值为 NULL

现在我需要做的是将值 (-1,0,1) 传递给一个 sql 语句，该语句确定要撤回的内容

-1 - should bring back all rows
0  - should bring back rows with a NULL date value
1  - should bring back rows with a valid date value

我尝试使用 CASE 语句，但逻辑运算符需要根据情况进行更改关于传递给查询的值。

让我们只调用表 Quotes 并完成列，所以...

CREATE TABLE 'Quotes'
(
    completed DATETIME default(NULL)
)

解决方案需要适用于 SQL Server 2000，并且我正在使用存储过程而不是动态 SQL。所以它真的需要在一份声明中全部体现。

谢谢担

原文

This problem has bugged me so many times and i have now decided to try and find the proper solution for it, instead of my long-winded/dirty/horrible way I always revert to (copying the sql statement)

The table has a date column with a default value of NULL

Now what i need to do is pass a value (-1,0,1) to an sql statement which determines what to pull back

-1 - should bring back all rows
0  - should bring back rows with a NULL date value
1  - should bring back rows with a valid date value

I have tried using CASE statements, but the logical operator would need to change depending on the value being passed to the query.

Lets just call the table Quotes and the column completed, so...

CREATE TABLE 'Quotes'
(
    completed DATETIME default(NULL)
)

Solution needs to be for SQL Server 2000, and i'm working in stored procedures not dynamic SQL. So it really needs to be all in one statement.

Thanks
Dan

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜血缘 2024-08-02 00:20:35

WHERE 子句中类似这样的内容

WHERE (@param = -1)
OR (@param = 0 AND completed IS NULL)
OR (@param = 1 AND completed IS NOT NULL)

Something like this in the WHERE clause

WHERE (@param = -1)
OR (@param = 0 AND completed IS NULL)
OR (@param = 1 AND completed IS NOT NULL)

回复收藏 0 原文

养猫人 2024-08-02 00:20:35

试试这个：

declare @param int
set @param=-1
declare @sql varchar(2000)
set @sql='select * from quotes '+ 
case @param when 0 then 'where completed is null'
            when 1 then 'where completed is not null'
            when -1 then ''
end
exec(@sql)

拉杰

Try this:

declare @param int
set @param=-1
declare @sql varchar(2000)
set @sql='select * from quotes '+ 
case @param when 0 then 'where completed is null'
            when 1 then 'where completed is not null'
            when -1 then ''
end
exec(@sql)

Raj

回复收藏 0 原文

~没有更多了~