如何在WHERE条件下选择全部. [英] How do I select all in the WHERE condition.

问题描述

您好，成员，

我有一组SQL语句，我将需要利用where条件，但是我想知道如何在where条件适当的情况下选择所有条件.因此，要使其全选，需要在参数中插入什么?

Hi members,

I have a set of SQL statement which I will need to make use of the where condition but I would like to know how to select all with the where condition in place. Thus what will be required to be inserted in the parameters to make it select all?

@param1 nvarchar(max),
@param2 nvarchar(max)

DECLARE @SQL nvarchar(max)


SET @SQL= 'SELECT *
FROM Table1
WHERE Column1 IN (@param1) AND Column2 IN (@param2)'

EXEC sp_executesql @SQL; 

我尝试将列名放入参数中，例如将column1放入@ param1中，但它不会返回我所有的数据.例如，table1由4524行组成，但是使用上面的表，它仅返回309行.我该如何解决?

I tried putting the column name in to the param e.g column1 into @param1 but it does not return me all the data. For example table1 consist of 4524 rows but using the above it only returns me 309 rows. How can I solve this?

推荐答案

WHERE 1 = 1

但是，为什么不完全摆脱WHERE子句呢?

But why not just get rid of the WHERE clause totally instead?

检查一下...

Hi,

Check this...

SELECT *
FROM Table1
WHERE Column1 = CASE WHEN @para_Col1 <> '' then @para_Col1 ELSE Column1 End
AND Column2 = CASE WHEN @para_Col2 <> '' then @para_Col2 ELSE Column2 End
AND Column3 = CASE WHEN @para_Col3 <> '' then @para_Col3 ELSE Column3 End
AND Column4 = CASE WHEN @para_Col4 <> '' then @para_Col4 ELSE Column4 End
--Note here i am assuming only four columns you have in your table1.

更新一个...

Update one...

@param1 nvarchar(max),
@param2 nvarchar(max)
 
SELECT *
FROM Table1
WHERE Column1 = CASE WHEN @param1 <> '' Then @param1 Else Column1 end
AND Column2 = CASE WHEN @param2 <> '' Then @param2 Else Column2 end

--i think there is no need of dynamic query.

根据您的条件，您应该开发一种逻辑以识别是否已选中全选".在此基础上，您可以按以下方式自定义查询.

As per your conditions, you should develop a logic to identify whether "Select All" has been checked or not. On that basis you can customize your query as below.

@param1 nvarchar(max),--pass 1 when Select All is checked
@param2 nvarchar(max),--pass 1 when Select All is checked

 
SELECT *
FROM Table1
WHERE Column1 IN CASE WHEN @param1 = '1' then Cloumn1 else @param1 end
AND Column2 IN CASE WHEN @param2 = '1' then Cloumn2 else @param2 end


--if You can do this Hard coding in your code then
SELECT *
FROM Table1
WHERE Column1 IN CASE WHEN @param1 = 'Cloumn1' then Cloumn1 else @param1 end
AND Column2 IN CASE WHEN @param2 = 'Cloumn2' then Cloumn2 else @param2 end

希望对您有帮助.

欢呼

Hope this will help you.

Cheers

好吧，您的评论
"让我们说在column1中有4个名称，但是用户可以选择tom和mary，这将传递给类似"tom，" mary的参数，但是，如果用户单击全选，它将被传递给param' 'column1'这是我当前的代码逻辑'

更改
之类的逻辑 将全选"作为参数之一传递到SP/Query，根据此参数在逻辑中写入查询

例如:

Hi As pert your comments
''lets say in column1 there are 4 names but user can select maybe tom and mary which It will pass into the parameter like this ''tom,''mary'' however if user click on select all it will pass into the param ''column1'' which are my current code logic''

change the logic like
Pass the ''select all'' as one of parameter to your SP/Query,based on this parameter write the query in your logic

ex:

declare @Selectall Bit
@param1 nvarchar(max),
@param2 nvarchar(max)
if @Selectall > 0
begin
   SELECT * FROM Table1 where 1=1
end
else
begin
   SELECT * FROM Table1 where column1 in (@param1)
end

将此作为参考尝试您的要求...

take this as refernce try with your requirement...

这篇关于如何在WHERE条件下选择全部.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！