如何将条件添加到SELECT查询中 [英] How to add if condition into the SELECT query

问题描述

我有一个返回标量值的存储过程，我想实现这样的查询:

Hi,

I have a stored procedure that return scalar value and I want to implement query like this:

SELECT @SUM = SUM(table1.id) FROM table1
IF (@param1 IS NOT NULL)
   INNER JOIN table2 ON table1.t2=table2.id
IF (@param2 IS NOT NULL)
   INNER JOIN table3 ON table1.t3=table3.id
WHERE IF (@param1 IS NOT NULL)
          table2.field=@field2
      IF (@param2 IS NOT NULL)
          AND table3.field=@field3

那么如何将这些条件添加到查询中呢?

预先感谢.

So how could I add these conditions into query?

Thanks in advance.

推荐答案

使用动态Sql

Use a Dynamic Sql

declare @strSql nvarchar(1000) 
declare @param1 nvarchar(10) = ''
declare @param2 nvarchar(10) = ''
set @strSql = 'SELECT @SUM = SUM(table1.id) FROM table1 ' 
IF (@param1 IS NOT NULL)
   set @strSql = @strSql + ' INNER JOIN table2 ON table1.t2=table2.id'
IF (@param2 IS NOT NULL)
   set @strSql = @strSql +' INNER JOIN table3 ON table1.t3=table3.id'
set @strSql = @strSql +' WHERE '
IF (@param1 IS NOT NULL)
          set @strSql = @strSql +' table2.field=@field2'
      IF (@param2 IS NOT NULL)
          set @strSql = @strSql +' AND table3.field=@field3'

print @strSql 
exec sp_executesql @strSql 

http://stackoverflow.com/questions/317377/sql-if-statement -in-where-clause-for-searching-数据库 [ ^ ]
http://stackoverflow.com/questions/87821/sql-if-clause-within- where-clause [ ^ ]

http://stackoverflow.com/questions/317377/sql-if-statement-in-where-clause-for-searching-database[^]
http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause[^]

一种简单的解决方案是，您需要编写两个sql查询，其中1个对param1不为null，另一个对param2不为null并使用UNION AL关键字组合结果

Hi,

the simple solution is that you need to write two sql queries 1 for param1 is not null and another for param2 is not null and combine the result using UNION AL keyword

这篇关于如何将条件添加到SELECT查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！