Stored Procs and Conditional Where Clause 1

[Smeat]

Hi All

Is it possible to have a conditional where clause in a stored procedure?

For example:

CREATE PROCEDURE dbo.EnquiryList
  @LocationID int

AS

SELECT ID, EnquiryDate
FROM
Enquiry

IF @LocationID > 0 THEN
  BEGIN
    WHERE LocationID = @LocationID
  END
ELSE
  BEGIN
    WHERE LocationID IS NULL
  END

Regards

Smeat

 

[bborissov]

CREATE PROCEDURE dbo.EnquiryList
  @LocationID int

AS

SELECT ID, EnquiryDate
FROM
Enquiry
WHERE ISNULL(LocationID,-1) = ISNULL(@LocationID,-1)

Where -1 is value you are very sure not presented in your data.
Not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Smeat]

Hi bborissov

Thanks for your response.

I have one further question:

Is it also possible to do something like the following:

CREATE PROCEDURE dbo.EnquiryList
  @LocationID int,
  @ExecutiveID int

AS

SELECT ID, EnquiryDate
FROM
Enquiry

WHERE

  EnquiryDate IS NOT NULL

IF @LocationID > 0 THEN
  BEGIN
    AND LocationID = @LocationID
  END

IF @ExecutiveID > 0 THEN
  BEGIN
    AND ExecutiveID = @ExecutiveID
  END

What I need to do is create a stored procedure that accepts filter criteria that may or may not be specified. If the criteria is specified then they must be used to filter the results, else they should be ignored.

Regards

Smeat

 

[bborissov]

This is not the same query. You want ALL records if some of the parameter IS NULL or matching records if the parameters is not NULL:

CREATE PROCEDURE dbo.EnquiryList
  @LocationID int,
  @ExecutiveID int
AS

SELECT ID, EnquiryDate
FROM
Enquiry
WHERE EnquiryDate IS NOT NULL                            AND
      (@LocationID IS NULL OR LocationID = @LocationID)  AND
      (@ExecutiveID IS NULL OR ExecutiveID= @ExecutiveID)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[GeertVerhoeven]

The solution provided by bborissov is the way to go.

Although it is possible to create dynamic sql in a stored procedure by using the sp_sqlexec stored procedure, you should avoid using it.

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[Smeat]

Thanks again for your responses though i'm not sure this does what I need.

My dilema is that I must ONLY apply a filter IF one is specified.

For example, if @LocationID is null then I should not apply this as a filter. If it is not null then I should apply this as a filter.

WHERE
  EnquiryDate IS NOT NULL

IF @LocationID IS NULL
  -- Don't apply filter
ELSE
  -- Do apply filter
  BEGIN
    AND LocationID = @LocationID
  END

Regards

Smeat

 

[bborissov]

Smeat,
take a gooood look in my WHERE clause. If you didn't pass @LocationID as parameter it is NULL by default, so

WHERE (@LocationID IS NULL  OR LocationID =  @LocationID)

will works for ALL records, because first part will be satisfied (@LocationID IS NULL). If you pass a value to @LocationID then second part of that condition will work and you will have all records that match the condition:

LocationID =  @LocationID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Smeat]

Hi bborissov

Sorry, I didn't read your reply properly first time around.

I'll try your solution first thing in the morning.

Thanks again for your help

Smeat

 

[Smeat]

Hi bborissov

I tried out your suggestion and it does exactly what I needed, have a star for taking the time to help.

Regards

Smeat