How to get null values in where clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends,

I have two table

1. FWB_SF_Opportunity (Does not contain VCID)<br />
2. FWB_VWSFData (Contains column VCID)

I had made SP with following query:

SQL

SELECT * FROM (
SELECT * FROM FWB_SF_Opportunity B 
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
) T
where T.VCID = isnull(@vcid,T.vcid)

The parameter @VCID can take values 5(records whose vcid = 5) or null(gets all records, including VCID whose value is null)

Problem is when @VCID = NULL, the result should contain all records also VCID whose value is null. But the where condition fails in this case, it only gives record whose VCID = 5 and not null.

any solution?

thanks in advance

Posted 4-Sep-12 18:27pm

dhage.prashant01

Add a Solution

Comments

dhage.prashant01 5-Sep-12 0:29am

I can solve this issue by using IF ELSE condition and repeating the same code.
But i want to handle in same query.
Any solution??

2 solutions

Solution 1

AFAIK, either you have to have a repeating code to achieve it OR you can pass on something else in case of NULL from your code (during query param creation) and handle the known value in SP, like pass -1 when it's NULL. This will make sure your query param always has a valid value leading to query result as per your need.

Posted 4-Sep-12 19:02pm

Sandeep Mewara

Comments

dhage.prashant01 5-Sep-12 1:05am

I have tried below code and works perfect for me :)
And thanks for your solution too, surely will try to work on it :)

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)

dhage.prashant01 · Accepted Answer · 2012-09-04T19:04:00

I tried following

VB

SELECT * FROM (
SELECT * FROM FWB_SF_Opportunity B 
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
) T
where
((isnull(@VCID,'') = '' AND 1 = 1)
 OR
 (isnull(@VCID,'') <> '' AND T.VCID = @VCID))

And it works fine
Hope guys who face same situation, get help out of it

Happy coding :)