By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

SQL NULL or Value

P: n/a
Hi
my prob is like this..

DECLARE @id int

SELECT *
FROM Table1
WHERE ID = @ID

if @ID is null , I want to select all the records, that ID is null
Thanks
Dish

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Dishan Fernando (di****@gmail.com) writes:
DECLARE @id int

SELECT *
FROM Table1
WHERE ID = @ID

if @ID is null , I want to select all the records, that ID is null


IF @ID IS NULL
SELECT * FROM Table1
ELSE
SELECT * FROM Table1 WHERE ID = @ID

You can collapse this into

SELECT * FROM Table1 WHERE ID = @ID OR @ID is NULL

However, if there is an index on ID, you probably want it to be used
if @ID has a value. The latter statement will result in the table
being scanned.

In case you have several columns you want to do this on, I have an
article on my web site that could be of interest:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
No , I want to do in in single sql,

if I use this:
SELECT * FROM Table1 WHERE ID = @ID OR @ID is NULL
and passing some value to @ID

result also may have null ids.
what I want is if I pass value to @ID or Null . It will selecting
acording to that

Jul 23 '05 #3

P: n/a
On 7 Jan 2005 00:35:17 -0800, Dishan Fernando wrote:
No , I want to do in in single sql,

if I use this:
SELECT * FROM Table1 WHERE ID = @ID OR @ID is NULL
and passing some value to @ID

result also may have null ids.
what I want is if I pass value to @ID or Null . It will selecting
acording to that


Hi Dishan,

If I understand you correctly, you want to return only the rows that have
ID equal to NULL if you pass @ID as NULL. Correct?

Try
SELECT Column1, Column2, ..., ColumnN
FROM Table1
WHERE ID = @ID
OR ( @ID IS NULL AND ID IS NULL )

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

P: n/a
As far as I know, if you have an ID which is null your database
structure is not very well thought out. The only way you could get a
null id is if you are doing an outter join on two tables, if the one
being joined does not have any values it would still return the values
from the first. You should not have any columns returning values on a
row without an id...
For instance:
SELECT Products.*, [Order Details].OrderID
FROM Products LEFT OUTER JOIN
[Order Details] ON Products.ProductID = [Order
Details].ProductID
WHERE ([Order Details].ProductID IS NULL)
Just my two cents.

Jul 23 '05 #5

P: n/a
On 7 Jan 2005 05:21:28 -0800, ujjc001 wrote:
As far as I know, if you have an ID which is null your database
structure is not very well thought out.


Hi ujjc001,

Yes, you are correct. I wanted to add that, but then forgot it when I
clicked Send. Thanks for the addition!!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #6

P: n/a
Thanks .. thats what I want... ;)

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.