Help in WHERE clause

Question

Post reply

Help in WHERE clause

David-250683

Hall of Fame

Points: 3452
More actions
October 19, 2007 at 3:42 pm

#62970

Code for a test table from the Northwind database is below.
Right now when @dummy = 'Sales Representative' I can get two Titles
When @dummy is NULL I get all
When @dummy is specified to something other than 'Sales Representative'
I get that specified title.
How can I get 'Sales Representative' and all NULL titles
when @dummy = 'Sales Representative'?
-- Create #TestTable below
The result set should look like the result set from this:
SELECT * FROM #TestTable
WHERE Title IS NULL
OR Title = 'Sales Representative'
USE Northwind
GO
SELECTEmployeeID,Title
INTO#TestTable
FROMNorthwind.dbo.Employees
UPDATE #TestTable
SETTitle = NULL
WHERE EmployeeID = 5
DECLARE @dummy varchar(25)
SELECT @dummy = 'Sales Representative' --NULL --'Inside Sales Coordinator'
SELECT * FROM #TestTable
WHERE Title LIKE @dummy or @dummy is null
ORTitle =
CASE
WHEN @dummy = 'Sales Representative' THEN [Want to return NULL]
END
DROP TABLE #TestTable

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Not sure why you're trying to do such a simple thing in such a difficult manner... but, using YOUR code...

SELECT * FROM #TestTable

WHERE Title LIKE '%'+@dummy+'%'[/b] or Title is null

OR Title =

CASE

WHEN @dummy = 'Sales Representative' THEN 'Vice President, Sales' --NULL won't work here

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David-250683 Hall of Fame Points: 3452 More actions · Answer 2

Jeff,

Thanks, I appreciate your help. I do it the way I know how until I learn a better way. Then once I learn a better way (learning from you) I do it that way until once again someone shows me a better way.

I was not able to get the desired result set and I must apologize that I could have been a bit clearer. This query represents one of the issues I'm facing as part of a larger query. The actual stored procedure has an input parameter with a default value of NULL. Presently, if unspecified the parameter is ignored, if specified then the query is filtered on the value of the parameter. Now the user wants when a certain value is specified to include the rows where that value is met AND the rows where the table value is NULL.

The code you provided will always include the NULL values instead of only when a certain value is passed.

In the example, the desired result is:

If @dummy = 'Sales Representative' then all the rows where title is 'Sales Representative' AND

all of the rows where title is NULL should be returned.

If @dummy is NULL then all rows should be returned.

If @dummy is 'Vice President, Sales' then only the rows where title is 'Vice President, Sales' should be returned.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Then Jeff's script should work for you.

You just misled him a little bit, so code should be simplified:

SELECT * FROM #TestTable

WHERE (@dummy is null OR Title = @dummy)

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

SELECT * FROM #TestTable

WHERE (isnull(Title,'Sales Representative') = @dummy)

will return the Sales Reps and nulls together but not with the VPs

David-250683 Hall of Fame Points: 3452 More actions · Answer 5

Thank you polecat! This is perfect and gives the desired result set.

DECLARE @dummy varchar(25)

SELECT@dummy = 'Sales Representative' --'Inside Sales Coordinator'--'Vice President, Sales'--NULL

SELECT * FROM #TestTable

WHERE (isnull(Title,'Sales Representative') = @dummy) or @dummy is null

When @dummy is null I get all results sets

When @dummy is 'Inside Sales Coordinator' or 'Vice President, Sales' I get the corresponding title

When @dummy is 'Sales Representative' I get 'Sales Representative' and all the rows where title is null.

Perfect!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

mrpolecat (10/21/2007)
SELECT * FROM #TestTable
WHERE (isnull(Title,'Sales Representative') = @dummy)
will return the Sales Reps and nulls together but not with the VPs

While this will work, is easy to read, and provides the desired results in one pass, you need to be aware that using a function against a column in a where clause or as a join condition will cause either a table scan or index scan on that column.

I would recommend this:

If @dummy Is Not Null

Begin

If @dummy = 'Sales Representative'

Begin

Select {columns} From table where title is null or title = @dummy

End

Else

Begin

Select {columns} From table where title = @dummy

End

Else

Begin

Select {columns} From table

End

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

David-250683 Hall of Fame Points: 3452 More actions · Answer 7

David-250683

Hall of Fame

Points: 3452

October 22, 2007 at 7:19 am

#744131

Thanks Jack. I will check this out.