CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sam
Sam

Reputation: 2772

Using conditional operations in conjuction with INTERSECT in SQL Server

I trying to write a stored procedure where I would like to test a passed in argument for certain value through an IF/Else statement and ultimately INTERSECT the result with another table. Something like the following as a non-working pseudo example.

ALTER PROCEDURE [dbo].[Search]
    @Keyword nvarchar(MAX),
    @ClasificationId int
AS
BEGIN
    SET NOCOUNT ON;

    IF (@Keyword != null)
        SELECT * FROM Table WHERE [Keyword] LIKE @Keyword
    ELSE 
        SELECT * FROM Table 

    INTERSECT

    IF (@Classification != null)
        SELECT * FROM Table WHERE [ClassificationID] = @ClassificationId
    ELSE 
        SELECT * FROM Table 
END

Upvotes: 3

Views: 902

Answers (2)

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

You can meet your requirement with a simple modification to each WHERE clause.

SELECT 
   * 
FROM 
   Table 
WHERE 
   @keyword IS NULL 
OR [Keyword] LIKE @Keyword
INTERSECT
SELECT 
  * 
FROM 
  Table 
WHERE 
  @ClassificationId IS NULL 
OR [ClassificationID] = @ClassificationId

Upvotes: 4

Transact Charlie
Transact Charlie

Reputation: 2203

this is easier done as a catch all type query - I recommend the dynamic sql approach as done properly: you'll get a good plan for each permutation of query.

Read this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Upvotes: 3

Related Questions