T-SQL syntax for a condtional where clause

I have a stored procedure
Alter PROCEDURE qryPieceIDbyPartID 
	-- Add the parameters for the stored procedure here
	@PartID int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT PieceID, SerialNumber from tblPieces where PartID = @PartID
	order by SerialNumber
END

Open in new window


When PartID = 0 then I do not want a WHERE clause.
I just want the result of
SELECT PieceID, SerialNumber from tblPieces ORDER BY SerialNumber

How do I code that in T-SQL?
LVL 26
Nick67Asked:
Who is Participating?
 
Nick67Connect With a Mentor Author Commented:
No Nulls!
Null happens, and I deal with it, but not here.

@sevensnake77's gone on a tangent.
An int (PartID) gets passed in.
If that PartID is zero then I want the functional equivalent of
SELECT PieceID, SerialNumber from tblPieces order by SerialNumber
If that PartID is anything else (But will never be null) I want
SELECT PieceID, SerialNumber from tblPieces where PartID = @PartID order by SerialNumber

I didn't know the syntax of T-SQL IF before I posted the question.
Scott's syntax @ ID: 40565140 is the same as mine @ ID: 40565092
I think @@sevensnake77 was composing as I was posting.

Now
<my bad>The eye ran over it
You wrote
WHERE @PartID = 0 OR
I saw
WHERE PartID = 0 OR
</my bad>
PartID = 0, that doesn't happen, so at first that made no sense.

But I am still a bit hazy on the logic, but I think I get it
WHERE @PartID = 0 OR PartID = @PartID
When @PartID = 0 that'll always be true and I'll get everything
WHERE @PartID = 0 OR PartID = @PartID
@PartID isn't zero, so that bit is false, but PartID = @PartID will be true for the desired subset.

But @Scott says that logic can be problematic?  In terms of the query optimizer not playing nice?
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
WHERE @PartID = 0 OR
   ( enter the where clause you want if @PartID <> 0 here )
0
 
Nick67Author Commented:
PartID is never zero
if I pass in zero, it's because I want to return the full set of results without a predicate.
Does this do it
    -- Insert statements for procedure here
    IF @PartID <> 0
		SELECT PieceID, SerialNumber from tblPieces where PartID = @PartID order by SerialNumber
	ELSE
		SELECT PieceID, SerialNumber from tblPieces order by SerialNumber

Open in new window

or it should it be done differently?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Seven priceFull StackCommented:
ok so you have to have the parameter but you do not want the parameter passed in the where clause this would be a basic select ignoring the parameter.

you can set the parameter to null
@PartID int = 0
set @partID = null
if not correct please be more clear. thanks
0
 
Seven priceFull StackCommented:
you can do
if (Len(@partid) > 0  )     
Begin
EnD
Else
BEgin
End

Open in new window

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>When PartID = 0 then I do not want a WHERE clause.
>if I pass in zero, it's because I want to return the full set of results without a predicate.
 
My solution works, and is a common practice for handling 'either filter by this ID value, or don't if it's 0/-1/NULL/whatever'.

To test, copy-paste the below code block into your SSMS and execute
Declare @id int 

CREATE TABLE #tmp (id int, name varchar(10)) 

INSERT INTO #tmp (id, name) 
VALUES (1, 'banana'), (2, 'grape'),  (3, 'banana'),  (1, 'orange'),  (2, 'watermelon')

-- Return everything
SET @id = 0	
SELECT * FROM #tmp WHERE @id = 0 OR @id = id

-- Now return the 2's
SET @id = 2
SELECT * FROM #tmp WHERE @id = 0 OR @id = id

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
For performance, clarity, and ease of coding (not having to deal directly with NULLs), I prefer to do it this way:


IF @PartID > 0
    SELECT PieceID, SerialNumber
    from tblPieces
    where PartID = @PartID
    order by SerialNumber
ELSE
    SELECT PieceID, SerialNumber
    from tblPieces
    order by SerialNumber


The problem with:
WHERE @id = 0 OR @id = id
is that you can get full tables even when you set @id to a specific value because SQL will use the same query plan from an earlier run where @id was 0.
0
 
Seven priceFull StackCommented:
this works i tested it.  I set the id at the top. if you change it to 1 or 2 it works.
Declare @id int 
set @id =0
CREATE TABLE #tmp (id int, name varchar(10)) 

INSERT INTO #tmp (id, name) 
VALUES (1, 'banana'), (2, 'grape'),  (3, 'banana'),  (1, 'orange'),  (2, 'watermelon')
if @id = 0
set @id = null

if len(@id) >0
 SELECT * FROM #tmp where id = @id

else
SELECT * FROM #tmp 

drop table #tmp

                                         

Open in new window

0
 
Scott PletcherSenior DBACommented:
It works only because you change 0 to NULL, which results in code that is a convoluted mess.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>if @id = 0
>    set @id = null
Can also be written in one line as  -->  IF ISNULL(@id, 0) = 0  

>if len(@id) >0
Is your @id variable a varchar?  I assumed numeric based on the T-SQL in the question.
0
 
Scott PletcherSenior DBACommented:
>> Can also be written in one line as  -->  IF ISNULL(@id, 0) = 0  <<

Not at all, he is doing the exact opposite: replacing 0 with NULL.

You can do it in one line with:

IF LEN(NULLIF(@id, 0)) > 0

which to me is actually less convoluted: as least you can directly see that NULL must be used to get a zero length instead of a zero value.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like we've cross-threaded, as expert sevensnake77 tested it, and I replied to that comment, but we haven't heard from the asker on how he's using NULLs, and at first glance I'm not seeing any reference to NULLs in any asker comments.

So I'll wait until the asker chimes in before commenting again.
0
 
Scott PletcherSenior DBACommented:
NULLs has nothing to do with the asker.  sevensnake77 needed it to make his code (testing the LEN() of the value) work, since the LEN() of a 0 value is 1.
0
 
Seven priceFull StackCommented:
I put that the first time. ScottPletcher,, he can even use Coalesce
0
 
Scott PletcherSenior DBACommented:
My code is slightly different.  Specifically, my code will treat a NULL value in @PartID the same as zero.  That is the reason I use "@PartID > 0" rather than "@PartID <> 0" to control the flow.
0
 
Nick67Author Commented:
That nuance
Specifically, my code will treat a NULL value in @PartID the same as zero.
escapes me.  I come from the MS Access end of things.
Trying either one with PartID being null
"@PartID > 0"
"@PartID <> 0"
will blow up VBA code, and probably a query in Access, too.
Boolean operator and concatenators you can sometimes get away with -- your result is Null, mind you.
But comparison and arithmetic operators blow up and complain that there's a null.

Why is T-SQl different and why would "@PartID > 0" treat a null as zero
Surely null > 0 isn't valid!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I come from the MS Access end of things.
The Access version of Nz(anything, 0) is ISNULL(anything, 0).  If it helps I have an article out there called Migrating your Access Queries to SQL Server Transact-SQL that is a grab bag of conversions.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> why would "@PartID > 0" treat a null as zero <<
It doesn't.  As always, it treats NULL as "unknown".

>> Surely null > 0 isn't valid! <<
Surely it is.  A typical Boolean comparison will yield T or F; If you allow nulls, you have to allow three-part results, T/F/NULL(unknown).  Thus the perfectly valid result of:
null > 0
is null.  Null is not T, therefore the T branch under "@PartID > 0" is not taken, the ELSE branch is taken.  Such a comparison must be true to take the "true branch".

Same thing in a WHERE clause.  Surely:
column_name > 0
has to be valid even when column_name might be Null.  If it is null, the result of that is NULL rather than true, and thus the WHERE condition is not true and the row won't be selected.
0
 
Nick67Author Commented:
@Scott
Boolean logic and null is always slippery.
And I get it.
It's not that null > 0 is valid, it's that IF  null > 0 is clearly NOT TRUE, and therefore the next statement(s) is/are not executed, but the ELSE branch does.

What's the nuance with this though:
That is the reason I use "@PartID > 0" rather than "@PartID <> 0" to control the flow.
Shouldn't null <> 0 evaluate out to null, too, and therefore not true?
0
 
Scott PletcherSenior DBACommented:
>> IF  null > 0 is clearly NOT TRUE <<

It's NOT "NOT TRUE", it's unknown.  That's more than just semantics.  The difference is that:
NOT "NOT TRUE" = true
NOT "unknown" = unknown.
0
 
Nick67Author Commented:
Ok,
Let me phrase it more carefully (damn Boolean and null and three-valued logic)
The workings of IF require the statement following it to evaluate definitively to TRUE if the next statement(s) is/are going to be executed. null > 0 does not evaluate as TRUE.  It evaluates as null.

It's NOT "NOT TRUE",
Slipperiness.
With null in play, the NOT operator gets to be quite useless
In a Venn diagram with null, true, and false, you can assert something is NOT True.
It's in the areas of null and false.
But you can't assert NOT(NOT True) = True because the (NOT true) includes nulls and null can be any value.  And therefore in theoretical terms, statements involving 'null = ' always drive persnickety folks mad.
I do get that, been at the rodeo for a while -- but every RDBMS has it's wrinkles around these things.
Like Access.  You can link to a SQL Server table with a bit field that permits nulls.  In SSMS, you can see the 1,0, and NULL quite clearly.  In Access, you only see -1, 0.  If you do a Count() and group by, though, you'll get three rows.  One with True and a count, and two with False and a count.  It's enough to drive you mad if you're not careful.

I think we have the same understanding -- but endless words have been expended on Booleans and nulls before -- and we could do it again.

In T-SQL practical terms, why is there a difference between
"@PartID > 0" rather than "@PartID <> 0"
0
 
Scott PletcherSenior DBACommented:
>> because the (NOT true) includes nulls <<

NO, it does NOT, as I noted above.  SQL will render a result of NOT TRUE only when it's *known* to be not true.

In standard logic, NOT true = false; any NULL comparison cannot yield a true or false result.
0
 
Scott PletcherSenior DBACommented:
>> In T-SQL practical terms, why is there a difference between
 "@PartID > 0" rather than "@PartID <> 0" <<

Because of NULL (as usual), and because of negative values.  I admit I sometimes use neg. values to convey different levels of "all" rows.  @PartID > 0 would properly screen out specific values which falling thru for NULL and/or neg. values.
0
 
Nick67Author Commented:
In standard logic, NOT true = false; No argument there!
any NULL comparison cannot yield a result except null.  No argument there either!
Null swallows everything.

The english to convey the ideas is easy to muddle.
@PatHartman and I have a fundamentally different view on the fun presented by null.
He likes setting positive conditions IF something = something then get busy
I go with negative conditions IF something <> something then get busy
Getting null in the mix and 'equals' goes south in a hurry.
But that's me.

Thanks to you all for your input!
0
 
Scott PletcherSenior DBACommented:
In general I don't like negative conditions -- I think they're much harder for most people to process:

True or False:
You do not receive $200 for passing GO in Monopoly?
0
 
Scott PletcherSenior DBACommented:
There's not an "off-hand chance" of using an old plan.  SQL will use whichever plan it generates first from then on (barring statistics being updated or some other event which forces SQL to re-gen the plan).  When the values go from all to one, or one to all, the wrong plan will be used.
0
 
Nick67Author Commented:
@Scott
I've gone with IF THEN syntax to avoid any possibility of squirreliness, but we have JimHorn's
My solution works, and is a common practice for handling 'either filter by this ID value, or don't if it's 0/-1/NULL/whatever'.
I have nothing to gainsay that, except that if it predictably returns wrong results, why would it be a common practice?
0
 
Scott PletcherSenior DBACommented:
I have nothing to back up that it actually is a common practice, but I'll accept that.

Often common practices are not the best things for performance, they're just what is easiest to do.

It's common practice to do:

WHERE ISNULL(cola, '') = ''

but it's 100% wrong.
0
 
Nick67Author Commented:
WHERE ISNULL(cola, '') = ''

You lost me there.
0
 
Scott PletcherSenior DBACommented:
I'll put it this way:

Common practice is not always best practice.

In this specific case, it can be terrible for performance.  If you do this on a (very) large table, you'll end up re-coding it to make they @variable check selective to get decent performance out of it.
0
 
Nick67Author Commented:
@JimHorn's suggestion will work, but @ScottPletcher suggests there's an off-hand chance of the query optimizer reusing an old plan and giving invalid results.  The accepted comment points to the syntax I will use in production, and also clarifies why JimHorn's suggestion works and is commonly deployed.

Thanks to all who commented and helped explain the logic behind their contributions.

Nick67
0
 
Scott PletcherSenior DBACommented:
CORRECTION to my phrasing earlier:
I wrote:
"
The problem with:
 WHERE @id = 0 OR @id = id
 is that you can get full tables
"

It should have read:
 is that you can get full table scans even when you provide a specific value for @id.
0
 
Nick67Author Commented:
Ah!
That makes much more sense.
You get the right results, but you may get crappy performance
WHERE ISNULL(cola, '') = ''
Was that good syntax?
Oops, my bad -- I'm coming from Access.
So that looks like
WHERE ISNULL(cola, DoubleQuote) = DoubleQuote which is nonsensical
You're coming from SQL Server
WHERE ISNULL(cola, EmptyString) = EmptyString makes much more sense

The proper way to do that?
WHERE EXISTS cola
0
 
Scott PletcherSenior DBACommented:
The only proper way to do it:

WHERE (colA IS NULL OR colA = '')

If you want to omit/skip NULL values, you can just do:

WHERE (colA = '')

If colA is NULL, it will never be "=" (or ">", or "<>", etc.) to any value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.