CASE and where clauses - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CASE and where clauses

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2014-10-28 : 06:26:44

I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this.

Here is a snip of the existing SPROC.

quote:

IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health'
BEGIN
SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID
WHERE
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
WHEN 'Rating - Reactivity' THEN detail.R
WHEN 'Rating - Health' THEN detail.H
END
LIKE @columnValue);

WITH SProductSummary AS
(
SELECT p.[SID], p.Product, p.SecondName, p.Manufacturer, p.Category,
ROW_NUMBER() OVER (ORDER BY
CASE @sortDir
WHEN 'DESC' THEN NULL ELSE 1
END ASC,
CASE @sortByFieldName
WHEN 'Product' THEN p.Product
WHEN 'CommonName' THEN p.SECONDNAME
WHEN 'Manufacturer' THEN p.MANUFACTURER
WHEN 'Category' THEN p.CATEGORY
END ASC,
CASE @sortByFieldName
WHEN 'SID' THEN p.SID
END ASC) AS RowNumberAsc,
ROW_NUMBER() OVER (ORDER BY
CASE @sortDir
WHEN 'ASC' THEN NULL ELSE 1
END DESC,
CASE @sortByFieldName
WHEN 'Product' THEN p.Product
WHEN 'CommonName' THEN p.SECONDNAME
WHEN 'Manufacturer' THEN p.MANUFACTURER
WHEN 'Category' THEN p.CATEGORY
END DESC,
CASE @sortByFieldName
WHEN 'SID' THEN p.SID
END DESC) AS RowNumberDesc
FROM S_Summary p
INNER JOIN S_Detail detail ON
p.SID = detail.SID
WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
WHEN 'Rating - Reactivity' THEN detail.R
WHEN 'Rating - Health' THEN detail.H
END
LIKE @columnValue
)

so this works fine but now I need to add 1 more thing:

if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:

quote:

WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
WHEN 'Rating - Reactivity' THEN detail.R
WHEN 'Rating - Health' THEN detail.H
END
LIKE @columnValue

is this possible? How can I do this?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 08:50:43

Did you try it? Does it give you the results you want?

tech_1
Posting Yak Master

129 Posts

Posted - 2014-10-28 : 09:08:05

No, I am asking HOW to incorporate such a query. Please provide full detailed explanation WITH code and not just speculation.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 09:35:01

"Please provide full detailed explanation WITH code and not just speculation."

Hey man, I'm a volunteer! Only have a few minutes a day to help out. (and I'm certainly not speculating!) The reason I asked if you had tried your solution is because it is valid SQL. If it doesn't work for you then you need to show at least a few rows of your bad results along with what the correct results should be.

tech_1
Posting Yak Master

129 Posts

Posted - 2014-10-29 : 09:31:18

I believe you have completely misunderstood and read the point I was trying to make.
Still requiring a solution for this. I am also a volunteer and many other volunteers provide such working examples or detail.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 10:01:57

The thing is, your code is valid. In fact, what you are proposing is similar to what I would do. (That is, my working example would look just like yours). Can you share what it is about your proposed solution that is not working? e.g. a few rows of your bad results along with what the correct results should be?

tech_1
Posting Yak Master

129 Posts

Posted - 2014-10-29 : 10:25:44

Thanks for your response.
There currently is NO code to do what I am asking. The code I posted does indeed work but I want to make a couple of amendments and that is where I need the help with since I am unable to compose the correct syntax.

so as I said, in this snip here:

quote:

WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
WHEN 'Rating - Reactivity' THEN detail.R
WHEN 'Rating - Health' THEN detail.H
END
LIKE @columnValue

I want to add a further condition where if a parameter is supplied with a value (@stringBranch for example), I want to do an AND condition to the existing condition. Furthermore, if a value is supplied for @stringBranch, I want to be able to join another table to the query.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-29 : 10:33:05

you can have two different queries for this
if @stringBranch is null
then
<your current query>
else
<your other query with extra join>

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 10:46:46

Or:


select ... -- your current query
left join joincte on @stringBranch is not null and ... -- other join criteria
... -- rest of your query

tech_1
Posting Yak Master

129 Posts

Posted - 2014-10-29 : 10:47:36

@ahmeds08 - thanks however this will cause all sorts of problems given the way the SPROC is constructed and because this is constructed within a CTE, it will not work the way you are intending it to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources