CODEWITHSUNDEEP
sql
Curtis Empson
Curtis Empson

Reputation: 13

Return all data if variable is NULL

I'm trying to write a query that returns data based on a user input variable (@VariantID).

WHERE sod.ProductVariantID = @VariantID

The problem is that if @VariableID is NULL, I want to return all data regardless of what sod.ProductVariantID equals.

What's the best way of doing this?

Upvotes: 1

Views: 70

Answers (2)

radar
radar

Reputation: 13425

Use OR condition , checking for NULL value or equal condition =.

WHERE @VariantID is NULL OR sod.ProductVariantID = @VariantID

Upvotes: 6

Karl Kieninger
Karl Kieninger

Reputation: 9129

If that's SQL Server you can use ISNULL as an alternative to @RADAR answer.

WHERE sod.ProductVariantID = ISNULL(@VariantID,sod.ProductVariantID)

Depending on the context of your query prefer use a control statement such as:

IF @VariantID IS NULL 
   SELECT ...
ELSE
   SELECT ... WHERE sod.ProductVariantID = @VariantID

Upvotes: 0

Related Questions