Varchar parameter Tinyint search criteria

Question

Post reply

Varchar parameter Tinyint search criteria

Jo6205

SSC Enthusiast

Points: 143
More actions
August 27, 2015 at 10:11 am

#320889

Hi All,
I'm working in a stored procedure (not mine) and need to update a parameter. When a user runs the report, there is an option to pull jobs based on status - open, closed, all. I've tried CASE, SELECT, IF-THEN, IIF, and many others to no avail.
How can I take the parameter entries of O or C and match them to 1 (open) or 2/3 (closed)? I know what I have written isn't correct but I'm not sure what else to try.
CASE
WHEN (@JobStatus = 'O' OR @JobStatus = 'o') SELECT JobStatus FROM JCJM WHERE JobStatus = 1
WHEN (@JobStatus = 'C' OR @JobStatus = 'c') SELECT JobStatus FROM JCJM WHERE JobStatus > 1
ELSE NULL
END

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

IF @JobStatus = 'O'

SELECT JobStatus FROM JCJM WHERE JobStatus = 1

IF @JobStatus = 'C'

SELECT JobStatus FROM JCJM WHERE JobStatus > 1

You don't need to check both cases unless the DB is set as case sensitive. The default is case insensitive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jo6205 SSC Enthusiast Points: 143 More actions · Answer 2

GilaMonster, first let me say I love your avatar.

Thanks for your quick response. When I use IF, I receive an error - Incorrect syntax near the keyword 'IF'.

I'm adding more code so you can see what's around the change. More info is better than not enough.

FROM #AllJobs WITH (NOLOCK)

LEFT JOIN JCCM WITH (NOLOCK) ON

#AllJobs.JCCo = JCCM.JCCo AND

#AllJobs.Contract = JCCM.Contract

WHERE ((#AllJobs.JCCo = @JBCo)

OR @JBCo = 0)

AND LTRIM(RTRIM(#AllJobs.Job)) >= LTRIM(RTRIM(@BegJob))

AND LTRIM(RTRIM(#AllJobs.Job)) <= LTRIM(RTRIM(@EndJob))

AND

IF @JobStatus = 'O'

SELECT JobStatus FROM JCJM WHERE JobStatus = 1

IF @JobStatus = 'C'

SELECT JobStatus FROM JCJM WHERE JobStatus > 1

and

JCCM.JBTemplate IS NOT NULL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

*sigh* It would have helped if you''d explained that it was part of a query...

IF is a control flow, it's used outside of queries only. Hence my previous post was a complete and total waste of time.

What are the possible values of JobStatus?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100956 More actions · Answer 4

I'd try a combined query, force it to recompile so SQL can ignore the unmatchable condition:

SELECT JobStatus

FROM dbo.JCJM

WHERE

(@JobStatus IN ('O', 'o') AND JobStatus = 1) OR

(@JobStatus NOT IN ('O', 'o') AND JobStatus > 1)

OPTION (RECOMPILE)

If you want/need to keep the queries separate, you'll gain some minor efficiency by putting an ELSE between them:

IF @JobStatus IN ('O', 'o')

SELECT JobStatus

FROM dbo.JCJM

WHERE JobStatus = 1

ELSE

SELECT JobStatus

FROM dbo.JCJM

WHERE JobStatus > 1

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jo6205 SSC Enthusiast Points: 143 More actions · Answer 5

I apologize. I'm new to stored procedures and didn't realize more info was needed. I'll keep looking around on the internet.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jo6205 (8/27/2015)
'll keep looking around on the internet.

Or you could answer my question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jo6205 (8/27/2015)
GilaMonster, first let me say I love your avatar.
Thanks for your quick response. When I use IF, I receive an error - Incorrect syntax near the keyword 'IF'.
I'm adding more code so you can see what's around the change. More info is better than not enough.
FROM #AllJobs WITH (NOLOCK)
LEFT JOIN JCCM WITH (NOLOCK) ON
#AllJobs.JCCo = JCCM.JCCo AND
#AllJobs.Contract = JCCM.Contract
WHERE ((#AllJobs.JCCo = @JBCo)
OR @JBCo = 0)
AND LTRIM(RTRIM(#AllJobs.Job)) >= LTRIM(RTRIM(@BegJob))
AND LTRIM(RTRIM(#AllJobs.Job)) <= LTRIM(RTRIM(@EndJob))
AND
IF @JobStatus = 'O'
SELECT JobStatus FROM JCJM WHERE JobStatus = 1
IF @JobStatus = 'C'
SELECT JobStatus FROM JCJM WHERE JobStatus > 1
and
JCCM.JBTemplate IS NOT NULL

First, you may want to read the first article I have referenced below in my signature block. It walks you through what you should post and how to post it to get the best possible responses to your questions.

Second, Scott alluded to the answer in his post, here is a snippet based on what you posted:

WHERE ((#AllJobs.JCCo = @JBCo)

OR @JBCo = 0)

AND LTRIM(RTRIM(#AllJobs.Job)) >= LTRIM(RTRIM(@BegJob))

AND LTRIM(RTRIM(#AllJobs.Job)) <= LTRIM(RTRIM(@EndJob))

AND ((@JobStatus = 'O' and JCJM.Jobstatus = 1) or

(@JobStatus = 'C' and JCJM.JobStatus > 1))

AND JCCM.JBTemplate IS NOT NULL

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 8

From what I can see, it looks like the following will work for you...

AND ((@JobStatus = 'O' AND JCCM.JobStatus = 1) OR (@JobStatus = 'C' AND JCCM.JobStatus > 1)

Also, get rid of the "WITH (NOLOCK)"s...

Edit... Lynn apparently types a bit faster than I do.

ScottPletcher SSC Guru Points: 100956 More actions · Answer 9

Similar to the first option above; you may need to adjust for your specific values:

AND ((@JobStatus IN ('O', 'o') AND JobStatus = 1) OR

(@JobStatus NOT IN ('O', 'o') AND JobStatus > 1))

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jo6205 SSC Enthusiast Points: 143 More actions · Answer 10

Thanks everyone for your help.

Lynn, I will read the article you've referenced before I post more questions.

Sean Lange SSC Guru Points: 286589 More actions · Answer 11

Jason A. Long (8/27/2015)
Also, get rid of the "WITH (NOLOCK)"s...

I agree but without an explanation it doesn't make any sense.

Here is an article that discusses this hint. There are plenty of other good articles explaining the evils of this hint.

http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jo6205 SSC Enthusiast Points: 143 More actions · Answer 12

Thank you.

I'm reading all I can to get up to speed.

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 13

Sean Lange (8/27/2015)
Jason A. Long (8/27/2015)
Also, get rid of the "WITH (NOLOCK)"s...
I agree but without an explanation it doesn't make any sense.
Here is an article that discusses this hint. There are plenty of other good articles explaining the evils of this hint.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

Good call, on adding an actual link to an explanation.

drew.allen SSC Guru Points: 76948 More actions · Answer 14

Jo6205 (8/27/2015)
Hi All,
I'm working in a stored procedure (not mine) and need to update a parameter. When a user runs the report, there is an option to pull jobs based on status - open, closed, all. I've tried CASE, SELECT, IF-THEN, IIF, and many others to no avail.
How can I take the parameter entries of O or C and match them to 1 (open) or 2/3 (closed)? I know what I have written isn't correct but I'm not sure what else to try.
CASE
WHEN (@JobStatus = 'O' OR @JobStatus = 'o') SELECT JobStatus FROM JCJM WHERE JobStatus = 1
WHEN (@JobStatus = 'C' OR @JobStatus = 'c') SELECT JobStatus FROM JCJM WHERE JobStatus > 1
ELSE NULL
END

It might also help to understand why what you tried didn't work.

1) Subqueries must always be enclosed in parens.

2) In a CASE statement, a WHEN clause requires a THEN clause.

3) A CASE statement can only return a single value. You need to make sure that any subqueries used to return a value in a CASE statement only returns one row and one column. You can use the TOP/BOTTOM keyword with an ORDER BY clause or you can use one of the aggregate functions to return a single value from multiple rows.

Rewriting your code with those in mind gives the following:

CASE

WHEN (@JobStatus = 'O' OR @JobStatus = 'o') THEN (SELECT MAX(JobStatus) FROM JCJM WHERE JobStatus = 1)

WHEN (@JobStatus = 'C' OR @JobStatus = 'c') THEN (SELECT MAX(JobStatus) FROM JCJM WHERE JobStatus > 1)

ELSE NULL

END

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA