Select query question

[John]

Hi

I am using below query to retrieve records from backend sql server 2005 db;

SELECT ID, Company ....
FROM tblClients
WHERE (Status = @status)

Problem is that sometimes I need all records regardless of what is status.
How can I achieve that? Can I add something like OR (Status ="") OR (Status
isNull) in the WHERE clause? If so, would using OR make the retrieval slow?

Thanks

Regards

 

[Jens K. Suessmeyer]

Hi,

this is actually a common used approach which would leave the plan as it is.

-jens.

 

[RobinS]

If you add "OR Status = "" " to your WHERE clause, you will only get
entries where the status is "". Same for Is Null.

Can't you just remove the WHERE clause?

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[Miha Markic [MVP C#]]

I think the query might get much slower in such case.
I would recommend to dynamically add where condition (or its parts) where
needed.

 

[Hari Prasad]

Hello,

Instead of dynamic SQL put a condition inside your procedure.

CREATE PROC USP_Proc1 @Status Int = NULL
AS
BEGIN
SET NOCOUNT ON
IF @Status !=NULL
SELECT ID, Company ....
FROM tblClients
WHERE (Status = @status)
ELSE
SELECT ID, Company ....
FROM tblClients
END

Thanks
Hari

 

[Erland Sommarskog]

I am using below query to retrieve records from backend sql server 2005
db;

SELECT ID, Company ....
FROM tblClients
WHERE (Status = @status)

Problem is that sometimes I need all records regardless of what is
status. How can I achieve that? Can I add something like OR (Status ="")
OR (Status isNull) in the WHERE clause? If so, would using OR make the
retrieval slow?

Depends. If there is no index on Status, or the optimizer does not use
that index, the queryu can't be slower and writing:

Status = @status OR @status IS NULL

will not make any difference. But if @status is selective, so that the
index is used, then it's better to use IF-ELSE.

From this follows that if you want to be on the safe side, you should
use IF-ELSE. Which is OK when then there is only a single column.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx