IS NULL, IS NOT NULL OR EITHER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IS NULL, IS NOT NULL OR EITHER

Author

Topic

lar
Starting Member

1 Post

Posted - 2011-01-07 : 18:15:59

Hello,

I'm using asp.net and passing a parameter to a stored procedure which is true or false. My problem is that I'm not sure how to create a condition that create the correct statement.

Example:
@dateContacted bit,

WHERE (dbo.Insurance_ContactForm.assignedagent IS NOT NULL) AND dbo.Insurance_ContactForm.SubmitDate BETWEEN @StartDate AND @EndDate AND dbo.Insurance_ContactForm.region IN
(SELECT IntValue From dbo.SpitToInt(@regionid)) AND TypeOfInsurance LIKE '%' + @product + '%' AND closed = @Closed AND Sold = @Sold AND dateContacted >>>>

Hope it some way to accomplish this

if @dateContacted is true then
dateContacted IS NOT NULL
if it is false then
dateContacted IS NULL
if it is not selected at all
NEITHER

Thanks Lar

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-07 : 18:38:22

If your intent is to conditionally apply the dateContacted piece of the where clause, you will have to do something like this:
The parameter for bit type would have to be one or the other.


IF @dateContacted = true 
BEGIN
Select stuff
From somewhere
WHERE (dbo.Insurance_ContactForm.assignedagent IS NOT NULL) AND dbo.Insurance_ContactForm.SubmitDate BETWEEN @StartDate AND @EndDate AND dbo.Insurance_ContactForm.region IN
(SELECT IntValue From dbo.SpitToInt(@regionid)) AND TypeOfInsurance LIKE '%' + @product + '%' AND closed = @Closed AND Sold = @Sold AND dateContacted is not null
END

IF @dateContacted = false 
BEGIN
Select stuff
From somewhere
WHERE (dbo.Insurance_ContactForm.assignedagent IS NOT NULL) AND dbo.Insurance_ContactForm.SubmitDate BETWEEN @StartDate AND @EndDate AND dbo.Insurance_ContactForm.region IN
(SELECT IntValue From dbo.SpitToInt(@regionid)) AND TypeOfInsurance LIKE '%' + @product + '%' AND closed = @Closed AND Sold = @Sold AND dateContacted is null
END


IF @dateContacted is null
BEGIN
Select stuff
From somewhere
WHERE (dbo.Insurance_ContactForm.assignedagent IS NOT NULL) AND dbo.Insurance_ContactForm.SubmitDate BETWEEN @StartDate AND @EndDate AND dbo.Insurance_ContactForm.region IN
(SELECT IntValue From dbo.SpitToInt(@regionid)) AND TypeOfInsurance LIKE '%' + @product + '%' AND closed = @Closed AND Sold = @Sold
END

The other option would be dynamic SQL.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 01:06:31

you can reduce it to:-


Select stuff
From somewhere
WHERE (dbo.Insurance_ContactForm.assignedagent IS NOT NULL) AND dbo.Insurance_ContactForm.SubmitDate BETWEEN @StartDate AND @EndDate AND dbo.Insurance_ContactForm.region IN
(SELECT IntValue From dbo.SpitToInt(@regionid)) AND TypeOfInsurance LIKE '%' + @product + '%' AND closed = @Closed AND Sold = @Sold 
AND ((dateContacted is not null AND @dateContacted = 'true') OR  (dateContacted is null AND @dateContacted = 'false') OR   @dateContacted is null)

change 'true','false' to 1,0 if field is of bit type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources