SQL select everything if parameter is zero, otherwise return a specific item

Question

Is there a way to write the following script so that it returns all products if the ProductID variable is null? And return a specific product when the product is not null. What I still have:

DECLARE @productID INT = NULL SELECT ProductID, ProductName, ProductDesc FROM product WHERE ProductID = @productID 

Answer 1

Case use example:

 SELECT ProductID, ProductName,ProductDesc FROM product WHERE ProductID = CASE WHEN @productID IS NULL THEN ProductID ELSE @productID END 

Or the IIF () function if you are using SQL Server 2012:

 SELECT ProductID, ProductName,ProductDesc FROM product WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID ) 

Answer 2

Why not just:

 DECLARE @productID INT = NULL SELECT ProductID, ProductName,ProductDesc FROM product WHERE ProductID = @productID OR @productID IS NULL; 

Here's a demo in SQLFiddle with NULL and value for @productID

Answer 3

try it

 DECLARE @productID INT = NULL SELECT ProductID, ProductName, ProductDesc FROM product WHERE ProductID = isnull(@productID,ProductID) 

Answer 4

 SELECT ProductID, ProductName, ProductDesc FROM product WHERE ProductID = CASE WHEN @productID IS NULL THEN ProductID ELSE @productID END 

Answer 5

Since "" is not recognized as NULL I used the value

 SELECT ProductID, ProductName,ProductDesc FROM product WHERE ProductID =IIF(@productID =1, ProductID, @productID ) 

In my code:

  MyDataAdapter.SelectCommand.Parameters("@productID").Value = 1 

Answer 6

Performance is incredibly better when using the CASE statement:

 SELECT ProductID, ProductName,ProductDesc FROM product WHERE ProductID = CASE WHEN @productID IS NULL THEN ProductID ELSE @productID END 

ISNULL() prevents the optimizer from using the index for this column.