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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Complex (to me!) Where statement

Author  Topic 

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 13:30:04
I have a stored proc that has 5 parameters. Based on the params, a dataset gets created for the user. The 5 params are:
Project, status, funder, start date and end date.

Ideally, what I want the user to enter a project and status, OR enter a funder, OR enter a start date, OR enter an end date

My where statement right now looks like this:

WHERE (
(p.project LIKE @project_ID + '%' AND p.status_pa LIKE @Active + '%')
OR (p.customer = @Funder)
OR (p.start_date >= Convert(datetime,(@startDate)))
OR (p.end_date <= Convert(datetime,(@endDate)))
)

But of course it doesn't work. Anyone able to point me to my error? My guess is that I am not nesting the statement correctly, but I don't know how to correct it.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-02-06 : 13:36:23
do you mean they are all optional?
i.e either Project & Status
or funder
or startdate
or end date?
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 13:41:24
Thats correct. If project and status are chosen, the others would be ignored, if funder was chosen, all the others would be ignored, etc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-02-06 : 13:52:53
I cant see any error with your where statement. WHy do you think its not working?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 13:59:01
One thing I noticed is that when you listed parameters in your question you said "status" but your statement showd "@active"
Here is one way to handle a bunch of optional parameters:

WHERE	p.project LIKE isNull(@project_ID + '%', p.project)
AND	p.status_pa LIKE isNull(@Active + '%', p.status_pa)
and	p.customer = isNull(@Funder, p.customer)
and	p.start_date >= isNull(@startDate, p.start_Date)
and	p.end_date <= isNull(@endDate, p.end_date)


Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 14:07:20
Using that where statement, if I select just a project and a staus, I get everything - every project and every status. If I select just a funder, I get every funder, not just the one entered. Its something I have been slowly building, so I started with just the project and status, which worked fine. Then I added the funder, and with just those three it worked correctly. So I added the start date parameter the way the funder was added. But now, selecting one funder gives everything, selecting a date gives what seems to be random dates, not the one supplied. Thats what made me think that the where statement isn't working.
The @active is just a badly named parameter - it does mean status!
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 14:36:49
>>I get everything - every project and every status. If I select just a funder, I get every funder, not just the one entered

Make sure you switched your "OR"s to "AND"s?

Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 14:49:19
I have tried that. I took your code, TG, and used that, as you have it (although I did add the CONVERT part to the dates) and I still am getting random results. I began by commenting out everything in the where statement. Then I started with just project and status params, entered a project and a status, and got about 100 records, which would be correct. I then added the funder param, entered the same project and status, and left the funder param as NULL, and got about 700 records - all different projects, all different status.
I don't know why!
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 15:18:58
Are you testing this from your application or from a query window? Because it sounds like when you are including the @funder value, the @project and @status values are going back to NULL.

And based on your original question, it sounds like that may be the intention. Are these parameters mutually exlcusive or any combination?
quote:
I want the user to enter a project and status, OR enter a funder, OR enter a start date, OR enter an end date



Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 15:43:19
I have tested from both, but currently I am using a query window, not the app. I want the params to be exclusive of each other, so that the user will EITHER enter a (project and status) OR a (funder). The (project and staus) combo will be used the most. If the project and status are left blank, then a Funder has to be entered. If that happens, then I want all records with that funder, regardless of project or status.
If someone were to just enter a start date, and no other params, I want a dataset that returns everything that starts on or after the date entered.
So, if the project/status combo is left blank, then ONE, and only one, of the others have to have something in them.

Gosh, I never thought it would be so hard to put into words what I am trying to do! Sorry if I am confusing the issue!
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 15:48:19
Well let's look at something we can all see. This is the way I intended the WHERE clause to work. Where any combination of parameters could be passed and all non-null parameter values would be honored. Is this the behavior that you are looking for?
If not, set the parameter list to what you want and post the "correct" result set:

declare @t table 
	(project varchar(10)
	,status_pa varchar(10)
	,customer varchar(10)
	,start_date datetime
	,end_date datetime)
insert @t
select 'p1', 'inActive', 'c1', getdate()-10, getdate() union all
select 'p1', 'active', 'c1', getdate()-5, getdate() union all
select 'p1', 'active', 'c1', getdate()-3, getdate() union all
select 'p1', 'active', 'c2', getdate()-3, getdate() union all
select 'p2', 'inActive', 'c1', getdate()-10, getdate() union all
select 'p2', 'active', 'c1', getdate()-5, getdate() union all
select 'p2', 'active', 'c1', getdate()-3, getdate() union all
select 'p2', 'active', 'c2', getdate()-3, getdate() 

declare @project_id varchar(10)
	,@active varchar(10)
	,@funder varchar(10)
	,@startDate datetime
	,@endDate datetime

------------------------------------------------------
--set your desired variables:

--set	@project_id = 'p1'
set	@active = 'active'
set	@funder = 'c1'
--set	@startDate = getdate()-10
--set	@endDate = getdate()+1
------------------------------------------------------

select	* from @t p
WHERE	p.project LIKE isNull(@project_ID + '%', p.project)
AND	p.status_pa LIKE isNull(@Active + '%', p.status_pa)
and	p.customer = isNull(@Funder, p.customer)
and	p.start_date >= isNull(@startDate, p.start_Date)
and	p.end_date <= isNull(@endDate, p.end_date)


Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 16:56:25
That seems to be right. I took that code, and put it in a query window, then tested my variables. I didn't comment them out, but put in NULL. For the project and status, I had the following, which was perfect.

set @project_id = 'p1'
set @active = 'active'
set @funder = NULL
set @startDate = NULL
set @endDate = NULL

If only funder gets selected, I had

set @project_id = NULL
set @active = NULL
set @funder = 'c1'
set @startDate = NULL
set @endDate = NULL

and that worked too. So, I can only think there is something basic wrong in my stored proc.

ALTER PROCEDURE [dbo].[sp_Linear_DonorReport]
@project_ID varchar(6), @Active varchar(10), @Funder varchar(6),@startDate datetime, @endDate datetime

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Create Table #tbl_LinearRpt(projectId char(6),
project_desc char(100),
customer char(10),
projSite char(10),
status_pa char(10),
rpm char(5),
rpmName char(50),
padm char(5),
padmName char(50),
begin_date datetime,
end_date datetime,
total_1400 money,
total_2300 money
)

--Create basic linear info from PJProj
If (@Active = 'zBoth') Select @Active = ''
INSERT INTO #tbl_LinearRpt(projectId, project_desc, customer,projSite, status_pa, rpm, rpmName, padm, padmName, begin_date, end_date, total_1400, total_2300)
SELECT distinct p.project, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, NULL, NULL
FROM PJPROJ p
WHERE p.project LIKE isNull(@project_ID + '%', p.project)
AND p.status_pa LIKE isNull(@Active + '%', p.status_pa)
and p.customer = isNull(@Funder, p.customer)
and p.start_date >= isNull(@startDate, p.start_Date)
and p.end_date <= isNull(@endDate, p.end_date)
ORDER BY p.project


--Update linear info to include PADM & RPN Names, and the projSite
Update l
Set padmName = (Select formalName from tblAbraData where projectId=l.projectId AND pen = padm),
rpmName = (Select formalName from tblAbraData where projectId=l.projectId AND pen = rpm),
projSite = (select case when count(distinct pe_id02) = 1 then min(pe_id02) else 'Many' end as [projSite]
from PJPent where project = l.projectId),
Total_1400 = (Select Sum([dramt]-[cramt])from tbl_Totals where projectId = l.projectId and acct = '1400'),
Total_2300 = (Select Sum([dramt]-[cramt])from tbl_Totals where projectId = l.projectId and acct = '2300')

FROM #tbl_LinearRpt l


Select * from #tbl_LinearRpt
Order by projectID


/*--Select everything needed for the report dataset
select t1.projectId, t1.project_desc, t1.customer, t1.projSite, t1.status_pa, t1.rpmName,
t1.padmName, t1.begin_date, t1.end_date, t2.total_1400, t2.total_2300
from #tbl_LinearRpt t1 left outer join
#tbl_Totals t2
on t1.projectID = t2.projectID
where t1.projectId LIKE @projectId + '%'*/

END

If I run a query on the underlying table (PJPROJ) and select * where project = '12%' and status = 'A' I get 74 records.

If I run this stored proc (I right click on it, and select 'Execute Stored Procedure...'), and enter @project = '12%' and @status = 'A', passing a NULL value for the other params, I get 6 records.

If I remove the other params from the stored proc, and only have @project and @status, and run it using '12%' and 'A', I get 74 records.

I really can't see where I am going wrong. Of course, if it turns out to be a typo I will cry, but at least I will know!
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 17:12:44
I never use that: "(I right click on it, and select 'Execute Stored Procedure...')"

I would use t-sql to call it. Maybe for <null> values you are supposed to leave the box alone rather than enter "NULL"? It may interperet you entry as a string: 'NULL' rather than a true NULL.

Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 17:41:56
I tried leaving the box alone, but then it errors out saying the param hasn't been provided. But I think you are right - there must be something wrong with how its interpreting the NULL value, because everything you said works perfectly.
I'll keep pugging along at it, and see what I can find.
Thanks so much for your help - I really appreciate it!
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 17:59:51
I made a stored proc from the code that you posted, TG, and tested it by right clicking and selecting "Execute stored procedure..". I passed a NULL value and it worked just fine.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 18:35:10
quote:

If I run a query on the underlying table (PJPROJ) and select * where project = '12%' and status = 'A' I get 74 records.

If I run this stored proc (I right click on it, and select 'Execute Stored Procedure...'), and enter @project = '12%' and @status = 'A', passing a NULL value for the other params, I get 6 records.


your adhoc query is "select * FROM"
your proc is "Select DISTINCT ... FROM"

That could explain why you get different number of rows.

Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-06 : 18:42:39
I tried removing the Distinct part, but it made no difference.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-06 : 18:52:13
so these two sections return different number of rows?

exec sp_Linear_DonorReport
	@project_id = '12%' 
	,@active = 'A'
	,@funder = null
	,@startDate = null
	,@endDate = null

--==========================================================================

declare @project_id varchar(6)
	,@active varchar(10)
	,@funder varchar(6)
	,@startDate datetime
	,@endDate datetime

select	@project_id = '12%' 
	,@active = 'A'
	,@funder = null
	,@startDate = null
	,@endDate = null

SELECT distinct p.project, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, NULL, NULL
FROM PJPROJ p 
WHERE p.project LIKE isNull(@project_ID + '%', p.project)
AND p.status_pa LIKE isNull(@Active + '%', p.status_pa)
and p.customer = isNull(@Funder, p.customer)
and p.start_date >= isNull(@startDate, p.start_Date)
and p.end_date <= isNull(@endDate, p.end_date)
ORDER BY p.project


Be One with the Optimizer
TG
Go to Top of Page

bmahony993
Yak Posting Veteran

58 Posts
Posted - 2008-02-07 : 11:38:01
No, those two sections give me 6 records back. If I change the params, and just keep @project and @active, as below, I get 74 records back.

exec sp_Linear_DonorReport
@project_id = '12%'
,@active = 'A'


SELECT distinct p.project, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, NULL, NULL
FROM PJPROJ p
WHERE p.project LIKE isNull(@project_ID + '%', p.project)
AND p.status_pa LIKE isNull(@Active + '%', p.status_pa)
ORDER BY p.project

So then, I would add the @funder param, but leave it NULL, and I am back to 6 records.

exec sp_Linear_DonorReport
@project_id = '12%'
,@active = 'A'
,@funder = NULL


SELECT distinct p.project, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, NULL, NULL
FROM PJPROJ p
WHERE p.project LIKE isNull(@project_ID + '%', p.project)
AND p.status_pa LIKE isNull(@Active + '%', p.status_pa)
and p.customer = isNull(@Funder, p.customer)

What I want to see is the same 74 records, and I can't see why it would only return 6.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-02-07 : 11:53:43
You must have NULL values in your data. This technique is not good for when the columns are nullable:
and p.customer = isNull(@Funder, p.customer)

if p.customer is null then it equates to "where NULL=NULL" which won't work.

perhaps this would do it (although any existing indexes on these columns would not be used):
and isNull(p.customer, 'noCust') = coalesce(@Funder, p.customer, 'noCust')


Be One with the Optimizer
TG
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site