sql query if parameter is null select all

sql query if parameter is null select all

Try this:

SELECT * 
FROM MY_TABLE 
WHERE @parameter IS NULL OR NAME = @parameter;

 

 

SQL select all if parameter is null else return specific item

回答1

Use case statement:

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

Or IIF() function if you’re using SQL Server 2012:

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

 

回答2

Why not just:

DECLARE @productID INT = NULL

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

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

 

作者:Chuck Lu    GitHub