Multiple conditional ANDs in SQL WHERE

lzdyzydy :

I am currently using a String in Java to run a SQL statement

String fruitName = "apple";
String colorOfFruit = a < b ? null : "red";
String sizeOfFruit = c > d ? null : "big";

String sql = "SELECT * FROM my_table WHERE fruit = ? " +
 if (colorOfFruit != null) sql += "AND color = ? ";
 if (sizeOfFruit != null) sql += "AND size = ? "
 sql += "ORDER BY fruit";


int i = 1;
PreparedStatement stmt = databaseConnection.prepareStatement(sql)
stmt.setString(i++, fruitName);
if (colorOfFruit != null) stmt.setString(i++, colorOfFruit);
if (sizeOfFruit != null) stmt.setString(i++, sizeOfFruit);

ResultSet rs = stmt.executeQuery();

Is there any way I can convert this to a SQL query that I can run with a CallableStatement in Java? I was thinking of creating a stored procedure, but I am not sure how I would conditionally have the AND statements.

CREATE PROCEDURE MyProcedure
    @fruitName NVARCHAR(50),
    @colorOfFruit NVARCHAR(50),
    @sizeOfFruit NVARCHAR(50)
AS
BEGIN
    SELECT * FROM my_table WHERE fruit = @fruitName
    -- add an and statement here if @colorOfFruit is not null
    -- add another and statement here if @priceOfFruit is not null
    ORDER BY fruit
END

Dale K :

Just use AND/OR logic e.g.

IF the parameter is null OR the parameter = the column value e.g.

SELECT *
FROM my_table
WHERE fruit = @fruitName
-- add an and statement here if @colorOfFruit is not null
and (@colorOfFruit is null or color = @colorOfFruit)
-- add another and statement here if @sizeOfFruit is not null
and (@sizeOfFruit is null or Size = @sizeOfFruit)
ORDER BY fruit

Collected from the Internet

Please contact [email protected] to delete if infringement.