Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing NULL values in a WHERE clause Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 3:32 PM
Points: 7, Visits: 35
I thought I had this worked out but when i started testing I was getting more records than expected.

What I want to do is select records that meet the specific condition of the parameters.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
id INT,
eq_id VARCHAR(10),
bl_id VARCHAR(10),
fl_id VARCHAR(10),
rm_id VARCHAR(10)
)

--===== Insert the test data into the test table
INSERT INTO #mytable
(id, eq_id, bl_id, fl_id, rm_id)
SELECT 1, 'AAAA','BBBB','CCCC', NULL UNION ALL
SELECT 2, 'EEEE', NULL, NULL, NULL UNION ALL
SELECT 3, NULL,'BBBB', 'CCCC', NULL UNION ALL
SELECT 4, NULL, 'QQQQ',NULL, NULL


select * from #mytable

DECLARE @eq_id varchar(10), @bl_id varchar(10), @fl_id varchar(10), @rm_id varchar(10)
set @eq_id = NULL
set @bl_id = 'BBBB'
set @fl_id = 'CCCC'
set @rm_id = NULL

select * from #mytable
WHERE (eq_id = @eq_id or @eq_id IS NULL) AND (bl_id = @bl_id or @bl_id IS NULL)
AND (fl_id = @fl_id or @fl_id IS NULL) AND (rm_id = @rm_id or @rm_id IS NULL)

When the last select statement gets run there are two rows returned and I understand why. What I am trying to get is the rows that meet the condition for each of the columns. There is only one row that should be returned but I can't figure out how to construct the where clause.

I did get it to work as expected using "??_id = @value" and SET ANSI NULLS OFF but this is running in a function.

Any ideas or direction would be much appreciated.

Thanks,
Craig
Post #1375811
Posted Tuesday, October 23, 2012 12:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Hi,

Try this article on Catch All Queries
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/




Clear Sky SQL
My Blog
Kent user group
Post #1375843
Posted Tuesday, October 23, 2012 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 3:32 PM
Points: 7, Visits: 35
Dave,

Thanks for the link. Some good stuff when thinking about dynamic SQL. I started down the dynamic path and was breaking apart the code and realized that I had an error in the WHERE clause. I get myself in more trouble with cut/paste.

I was checking if the passed parameter was NULL not the field. I should of had:
select * from #mytable
WHERE (eq_id = @eq_id or eq_id IS NULL) AND (bl_id = @bl_id or bl_id IS NULL)
AND (fl_id = @fl_id or fl_id IS NULL) AND (rm_id = @rm_id or rm_id IS NULL)

I am pretty sure this is giving me what I need.

Sometimes just asking the question gets things going.

Thanks,
Craig
Post #1376194
Posted Friday, October 26, 2012 6:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 11:26 PM
Points: 116, Visits: 397
Hello,
Go to this link efficent way to pass All, Null , multiple value
http://www.bi-rootdata.com/2012/09/efficient-way-of-using-all-as-parameter.html
Post #1377527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse