How do I select all in the WHERE condition.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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?

SQL

@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;

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?

Posted 6-Oct-15 19:21pm

waynetan123

Updated 6-Oct-15 19:40pm

v3

Add a Solution

Comments

Maciej Los 7-Oct-15 2:37am

SELECT * FROM Table1 returns all data.

3 solutions

Solution 3

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:

SQL

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...

Posted 6-Oct-15 20:32pm

vangapally Naveen Kumar

Comments

waynetan123 7-Oct-15 2:39am

I get what you are trying to do but I do have more than 1 parameters to be passed so I don't think this can work.

vangapally Naveen Kumar 7-Oct-15 3:26am

If you are using select all for every column??
if it the case put an flag based on the flag do the code changes,lets say column for select all and column2 for also selectall then set the flag to 1 else 0

morover you can pass the 'n' number of parameters in where clause.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2015-10-06T19:27:00

Solution 1

SQL

WHERE 1 = 1

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

Posted 6-Oct-15 19:27pm

phil.o

Comments

waynetan123 7-Oct-15 1:28am

The reason being is actually Im passing in parameters thus sometimes it can be based on condition and sometimes it can be select all. How do I use the where 1 = 1? I need the in clause actually

waynetan123 7-Oct-15 1:41am

Please refer to my updated questions thanks

Maciej Los 7-Oct-15 2:35am

+5!

Magic Wonder · Accepted Answer · 2015-10-06T19:34:00

Solution 2

Hi,

Check this...

SQL

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...

SQL

@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.

SQL

@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

Posted 6-Oct-15 19:34pm

Magic Wonder

Updated 7-Oct-15 0:07am

v4

Comments

waynetan123 7-Oct-15 1:36am

Hi I have more than 4 columns but only 4 columns requires the where condition

Magic Wonder 7-Oct-15 1:43am

You can use CASE in where clause, it will work and complete your requirements.

Magic Wonder 7-Oct-15 1:44am

Try my solution.

waynetan123 7-Oct-15 1:49am

If I have lets say where Column1 = Column1 and Column2 = Column2 it will return me lesser rows as compared to the actual table. I tried as explain in my question thanks

Magic Wonder 7-Oct-15 1:53am

You mean to say, irrespective of where clause/parameter you need all rows???

waynetan123 7-Oct-15 1:56am

lets say if im want to have all rows, I will be passing in the column name into the parameter to get all the rows but it does not when I tried but it the even if I do not want all rows it shld pass in the required conditions into the parameter

Magic Wonder 7-Oct-15 1:57am

You are passing column name as parameters??? Am i write?

Magic Wonder 7-Oct-15 2:01am

well i am confused now!!! What is your requirement? What are the details you are passing in parameters? And why are you creating Dynamic query?

waynetan123 7-Oct-15 2:04am

The reason why I am creating dynamic query is because I will be passing in selections from a front end website. Thus users are able to select all or they can select the conditions they want. thus if they select specific condition it will then be passed into a string to be placed in a parameter but as for select all it will pass in the column name which passing in the column name does not work as it does not all me to retrieve all data . thanks

Magic Wonder 7-Oct-15 2:11am

Well, if user's do limited selection from front end then you are passing selected column's name. And if checks select all then data from all columns should come. Am i write?

waynetan123 7-Oct-15 2:15am

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

Magic Wonder 7-Oct-15 2:49am

Check my updated solution.

waynetan123 7-Oct-15 3:01am

sry I cant pass 1 as param when select all is check it does not work

Magic Wonder 7-Oct-15 3:35am

In that Case, what will you pass if user selects All?

waynetan123 7-Oct-15 4:55am

it will pass the column name. eg column1 if column name is column1

Magic Wonder 7-Oct-15 5:59am

Can not you pass something else like flag? As for select all also you are passing "Column Name" that indicates you made some provision in your logic to do that. Can not you change your logic?

Magic Wonder 7-Oct-15 6:04am

You have to do Flagging as if you are passing column name and as well selected values with same parameter then it will treat both as either column values or column name.

Magic Wonder 7-Oct-15 6:07am

Check updated solution....

--If you can do hard coding ...then

Magic Wonder 7-Oct-15 1:56am

Do not use where clause.

Simply write "Select * from Table1"

waynetan123 7-Oct-15 1:41am

Please refer to my updated questions thanks