Dynamic Where Clause in MySQL

Question

Is it possible to build a dynamic where clause in a stored procedure such that if the parameter is empty, you select all item in the table? However, if you do have a parameter, you select items where the condition is true?

You can do it dynamically or with a simple where clause: where (@id is null or id = @id). — Gordon Linoff
– Gordon Linoff, Commented Sep 29, 2015 at 2:40
if i have a procedure called test and I want to select all, would I do call test(null)? — dcart1234
– dcart1234, Commented Sep 29, 2015 at 2:48
if i do call test(), it would say incorrect number of arguments — dcart1234
– dcart1234, Commented Sep 29, 2015 at 2:49

japzdivino · Accepted Answer · 2015-09-29 08:32:13Z

2

Here is the trick... you can create a string query for your select command, and then manipulate it by conditional statement.. see sample below.. hope you can apply the logic on your work.

    -- Put a default value `NULL` to your parameter first
    CREATE TABLE TableNameHere 
    (
      @ParameterName VARCHAR(MAX) = NULL
    )

    DECLARE @SQLQuery VARCHAR(MAX)

    --Initialize SQL statement first
    --It will return true always , therefor it will display all data
    SET @SQLQuery = 'SELECT * FROM TableNameHere WHERE 1 = 1' 

    -- If parameter has a value then do the where condition inside the block
    IF @ParameterName IS NOT NULL
        BEGIN
           SET @SQLQuery = @SQLQuery + ' AND ColumnNameHere = ''' + @ParameterName + ''''
        END

    EXEC(@SQLQuery)

edited Sep 29, 2015 at 8:32

answered Sep 29, 2015 at 7:58

japzdivino

1,7463 gold badges17 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Dynamic Where Clause in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related