sql query using where condition

Question

4.00/5 (1 vote)

See more:

I have 4 variables coming into a stored procedure from c#. i must pass all the variables. This variables can either have a value or be null.

If the particular variable is null, I need to select all the rows in the table (some with NULL values, some with actual data).

If the variable is not null, I only need to select the rows where the variable matches a column.
i tried giving

SQL

SELECT FirstName FROM AdmissionTbl WHERE (City=@city or City is null) and (state=@state or state is null) and (religion=@religion or religion is null)and (marks=@marks or marks is null)

if a value is passed it works, but if it is null it is not retrieving all the records.
Can anyone helpme?

Posted 15-Feb-13 19:28pm

sukumari1

Updated 15-Feb-13 19:35pm

demouser743

v2

Add a Solution

Comments

demouser743 16-Feb-13 1:37am

Will all the values be null or particular values or null

sukumari1 16-Feb-13 1:56am

based on user selection; values may or may not be null

2 solutions

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)

Bandi Ramesh · Answer 1 · 2013-02-15T19:39:00

Quote:
i tried giving
SELECT FirstName FROM AdmissionTbl WHERE (City=@city or City is null) and (state=@state or state is null) and (religion=@religion or religion is null)and (marks=@marks or marks is null)

you are almost correct but a small change

SQL

SELECT FirstName FROM AdmissionTbl 
WHERE (@city is null or City=@city) 
and (@state is null or state=@state) 
and (@religion is null or religion=@religion) 
and (@marks is null or marks=@marks)

Hope it helps you!

demouser743 · Answer 2 · 2013-02-15T20:13:00

Hi check here I don't know how you build your table I have create a table with ID, Firstname,city and retrieving all firstname when city is matched or city is null

SQL

CREATE TABLE [dbo].[tblDemo]([id] [int] NOT NULL,[FirstName] [varchar](50) NULL,
[City] [varchar](50) NULL) ON [PRIMARY]

http://s8.postimage.org/4oukaxwtx/sql.jpg[^]

this is my query to retrieve the firstname when city is null or value is passed

SQL

Select FirstName From tbldemo Where city is null or city like 'RJY'

http://s4.postimage.org/e4qlni62l/results.jpg[^]

sql query using where condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0