Intelligent SP WHERE parameter

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 2000 Forums

Transact-SQL (2000)

Intelligent SP WHERE parameter

Author

Topic

dee-u
Starting Member

14 Posts

Posted - 2009-03-22 : 21:41:41

Below is my test case. SP_STATUS can be 'Y' or NULL. I would want it that if @whereSP_STATUS is not equal to 'Y' (a blank string '' or null) then it should return all records from table SITES.

DECLARE  @whereSP_STATUS NVARCHAR(1) 

SET @whereSP_STATUS = NULL

SELECT SITES.REFERENCE, 
       SITES.SP_STATUS 
FROM   SITES 
WHERE  SITES.SP_STATUS = COALESCE(@whereSP_STATUS, SITES.SP_STATUS)

I am at loss on how can this be done. Any help will be highly appreciated.

dee-u
Starting Member

14 Posts

Posted - 2009-03-22 : 23:35:14

The following seem to do the trick.


DECLARE  @whereSP_STATUS NVARCHAR(1) 

SET @whereSP_STATUS = NULL

SELECT SITES.REFERENCE, 
       SITES.SP_STATUS 
FROM   SITES 
WHERE  @whereSP_STATUS IS NULL OR SITES.SP_STATUS = @whereSP_STATUS

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-23 : 09:25:51

Unless I am missing something, wouldn't something like this work better?

SELECT SITES.REFERENCE,
SITES.SP_STATUS
FROM SITES
WHERE SITES.SP_STATUS <> 'Y'

Yours states where the variable @whereSP_STATUS is null (which it always is based on your set statement "SET @whereSP_STATUS = NULL") or SITES.SP_STATUS = @whereSP_STATUS (which is null). This would return all rows from SITES, not only the ones you would expect.

Terry

-- Procrastinate now!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-23 : 10:12:24

quote:
Originally posted by tosscrosby

Unless I am missing something, wouldn't something like this work better?

SELECT SITES.REFERENCE,
SITES.SP_STATUS
FROM SITES
WHERE SITES.SP_STATUS <> 'Y'

Yours states where the variable @whereSP_STATUS is null (which it always is based on your set statement "SET @whereSP_STATUS = NULL") or SITES.SP_STATUS = @whereSP_STATUS (which is null). This would return all rows from SITES, not only the ones you would expect.

Terry

-- Procrastinate now!

This condition will not be true if SITES.SP_STATUS is NULL.

WHERE SITES.SP_STATUS <> 'Y'

Example:

select
	*
from
	(
	select SP_STATUS = 'Y', x =1 
	union all
	select SP_STATUS = 'N', x =2
	union all
	select SP_STATUS = null, x =3 
	) SITES
WHERE SITES.SP_STATUS <> 'Y'

Results:
SP_STATUS x           
--------- ----------- 
N         2

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources