Using conditional operations in conjuction with INTERSECT in SQL Server

Question

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

ClassificationID is a NVARCHAR(MAX)? Unlike most <ID> fields no? — Transact Charlie
– Transact Charlie, Commented May 29, 2013 at 19:47

Paul Alan Taylor · Accepted Answer · 2013-05-29 19:33:55Z

4

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

answered May 29, 2013 at 19:33

Paul Alan Taylor

10.7k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Transact Charlie · Accepted Answer · 2013-05-29 19:31:49Z

3

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/

answered May 29, 2013 at 19:31

Transact Charlie

2,21318 silver badges15 bronze badges

1 Comment

Sam Over a year ago

Interesting article. Thanks

Collectives™ on Stack Overflow

Using conditional operations in conjuction with INTERSECT in SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related