October 22, 2012 at 9:34 pm
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
(
idINT,
eq_idVARCHAR(10),
bl_idVARCHAR(10),
fl_idVARCHAR(10),
rm_idVARCHAR(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
October 23, 2012 at 12:21 am
Hi,
Try this article on Catch All Queries
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
October 23, 2012 at 11:59 am
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
October 26, 2012 at 6:01 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply