我不想使用if else用于非常小的目的,它让我一次又一次地在块中编写代码。 如何使用AND / OR运算符编写它:
if ( @ datafor year = 0) 选择 * 来自 abc else 选择 * 来自 employee 其中 dataforyear = @ dataforyear解决方案
我想你提到了两个不同的这两个SELECT查询中的表名。我喜欢这样的东西。查看该文章以获取更多示例。 DECLARE @ dataforyear AS NUMERIC DECLARE @ SQLQuery AS NVARCHAR ( 500 ) SET @dataforyear = 0 SET @SQLQuery = ' SELECT * FROM employee' IF ( @ dataforyear <> 0 ) BEGIN @ SQ LQuery = @ SQLQuery + ' WHERE dataforyear =' + @ dataforyear END EXECUTE sp_executesql @ SQLQuery
在存储过程中构建动态SQL [ ^ ]
这应该符合你的目的。 选择 * 来自员工 其中(@ dataforyear = 0 OR dataforyear = @ dataforyear)
您的要求非常简单,当然,如果您知道SQL中的NULLIF,COALESCE函数,它也不需要使用AND / OR。您的查询可以修改如下。 SELECT * FROM 员工 WHERE dataforyear = COALESCE ( NULLIF ( @ dataforyear , 0 ),dataforyear)
说明: 如果@dataforyear = 0,则NULLIF(@ dataforyear,0)将变为NULL。然后COALESCE(NULLIF(@dataforyear,0),dataforyear)将为COALESCE(NULL,dataforyear)为您提供数据作为回报。 如果@dataforyear<> 0然后NULLIF(@dataforyear,0)将成为@dataforyear。然后COALESCE(NULLIF(@dataforyear,0),dataforyear)将COALESCE(@dataforyear,dataforyear)给你@dataforyear作为回报。 享受quries的乐趣。
I dont want to use if else for very small purpose which took me to write code again and again in the blocks. How to write it using AND / OR operator:
if (@datafor year=0) select * from abc else select * from employee where dataforyear=@dataforyear 解决方案 I think you have mentioned two different table names in those two SELECT query. I prefer something like this. Check that article for few more examples. DECLARE @dataforyear AS NUMERIC DECLARE @SQLQuery AS NVARCHAR(500) SET @dataforyear = 0 SET @SQLQuery = 'SELECT * FROM employee ' IF (@dataforyear <> 0) BEGIN @SQLQuery = @SQLQuery + ' WHERE dataforyear = ' + @dataforyear END EXECUTE sp_executesql @SQLQueryBuilding Dynamic SQL In a Stored Procedure[^]
This should serve your purpose. select * from employee where (@dataforyear=0 OR dataforyear=@dataforyear)Your requirement is very simple and of course it has no need to use AND/OR also, if you know NULLIF, COALESCE functions in SQL. Your query can be modified as follows. SELECT * FROM Employee WHERE dataforyear = COALESCE(NULLIF(@dataforyear, 0), dataforyear)Explanation: If @dataforyear = 0 then NULLIF(@dataforyear, 0) will become NULL. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(NULL, dataforyear) gives you dataforyear in return. If @dataforyear <> 0 then NULLIF(@dataforyear, 0) will become @dataforyear. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(@dataforyear, dataforyear) gives you @dataforyear in return. Have fun with quries.
更多推荐
如果使用“AND”,如何使用/“SQL中的OR运算符
发布评论