WHERE clause question

[DerPflug]

I have a stored procedure that is called by a windows application that returns a resultset that populates a datagrid. The app gives the option to enter a customer id to narrow down the search by customer id. Using one query, how might I write my where clause to either bring back records only matching the passed in customer id or all records if I pass in a zero (which is what I do if no customer id was entered). Thanks in advance.

 

[DotNetGnat]

we only introduce the customer_id criteria in the where clause only if its is not null..so something like this

if customer_id<>'' then
....WHERE customer_id

-DNG

 

[gmmastros]

You can do this in a Stored Procedure also.

Create Procedure SelectRecords
@IdField Integer
As

Select * From Table
Where IdField = Case When IsNull(@IdField, 0) = 0
Then IdField
Else @IdField
End

This works because when you send NULL or 0 in to the sproc, the case statement becomes the equivalent of...

Select * from Table Where IdField = IdField

When you pass a parameter, the case statement becomes...

Select * From Table Where IdField = <some number>

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JasonL99]

Try

WHERE ((@IdField = 0) OR (IdField = @IdField))

 

[DerPflug]

JasonL99, I had already implemented your suggestion. Works great.