SQL NULL or Value

[Dishan Fernando]

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

[Erland Sommarskog]

Dishan Fernando (di****@gmail.c om) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dishan Fernando]

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

[Hugo Kornelis]

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)

[ujjc001]

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.Produc tID = [Order
Details].ProductID
WHERE ([Order Details].ProductID IS NULL)
Just my two cents.

[Hugo Kornelis]

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)

[Dishan Fernando]

Thanks .. thats what I want... ;)