Solved

T-SQL syntax for a condtional where clause

Posted on 2015-01-22
34
106 Views
Last Modified: 2015-01-28
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?
0
Comment
Question by:Nick67
  • 15
  • 10
  • 5
  • +1
34 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40565076
WHERE @PartID = 0 OR
   ( enter the where clause you want if @PartID <> 0 here )
0
 
LVL 26

Author Comment

by:Nick67
ID: 40565092
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
 
LVL 9

Expert Comment

by:sevensnake77
ID: 40565094
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
 
LVL 9

Expert Comment

by:sevensnake77
ID: 40565099
you can do
if (Len(@partid) > 0  )     
Begin
EnD
Else
BEgin
End

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40565111
>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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 40565140
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
 
LVL 9

Expert Comment

by:sevensnake77
ID: 40565163
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40565179
It works only because you change 0 to NULL, which results in code that is a convoluted mess.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40565190
>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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40565208
>> 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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40565232
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40565268
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
 
LVL 9

Expert Comment

by:sevensnake77
ID: 40565279
I put that the first time. ScottPletcher,, he can even use Coalesce
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
ID: 40565295
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40565326
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
 
LVL 26

Author Comment

by:Nick67
ID: 40565640
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40566339
>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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 300 total points
ID: 40566502
>> 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
 
LVL 26

Author Comment

by:Nick67
ID: 40566727
@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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40566814
>> 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
 
LVL 26

Author Comment

by:Nick67
ID: 40566906
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40566969
>> 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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40566979
>> 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
 
LVL 26

Author Comment

by:Nick67
ID: 40567031
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40567074
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40567085
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
 
LVL 26

Author Comment

by:Nick67
ID: 40567116
@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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40567119
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
 
LVL 26

Author Comment

by:Nick67
ID: 40567126
WHERE ISNULL(cola, '') = ''

You lost me there.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40567156
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
 
LVL 26

Author Closing Comment

by:Nick67
ID: 40574826
@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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40575180
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
 
LVL 26

Author Comment

by:Nick67
ID: 40575500
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40575507
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now