Solved

isnull in where clause

Posted on 2010-11-30
6
266 Views
Last Modified: 2012-05-10
I want to put the following code in my query where statement: isnull(criteria,'1=1').

the critieria will either be something like "Age < 18", "Age > 18", etc,  or NULL. I would love for there to be a SQL eval statement but there is not.  also i want to stay away from dynamic SQL.......is thre anything that i can do here or do i have to go to dynamic SQL?
0
Comment
Question by:DB_Fury
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34240258
you will need to use IS NULL not ISNULL.

ISNULL is a function that replaces a NULL values with a user specified one.
0
 
LVL 1

Author Comment

by:DB_Fury
ID: 34240272
yeah thats what i want becuase it the criteria field is null in the database i just want to put something that will always be true there so there isjust just a and with a blank in the syntax
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 34240312
WHERE ISNULL(AGE,0)<18 AND ISNULL(AGE,0)>18
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:DB_Fury
ID: 34240347
i cant put anything static becuase it could be null, it could be Age > 20. so hardcoding it wont work.  i wish there was a eval funcation
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34240371
If the age is 20 is wont be nulland will not therefore be replaced with 0. Only in the event of a null value will it be replaced.
0
 
LVL 1

Author Closing Comment

by:DB_Fury
ID: 34240519
I was confused at first but now i understand.  thanks very much
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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