Sfrgttk

Sometimes a banana is just a banana

The need of reserving one's ability in job interviews

Should I use HTTPS on a domain that will only be used for redirection?

Practical reasons to have both a large police force and bounty hunting network?

Where is this quote about overcoming the impossible said in "Interstellar"?

In which way proportional valves are controlled solely by current?

A peculiar integral identity

If nine coins are tossed, what is the probability that the number of heads is even?

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

Is divide-by-zero a security vulnerability?

When to use mean vs median

Caulking a corner instead of taping with joint compound?

Did Amazon pay $0 in taxes last year?

Ahoy, Ye Traveler!

PTIJ: What’s wrong with eating meat and couscous?

Wardrobe above a wall with fuse boxes

Meaning of word ягоза

When do _WA_Sys_ statistics Get Updated?

Correct physics behind the colors on CD (compact disc)?

How to merge row in the first column in LaTeX

Formatting a table to look nice

Split a number into equal parts given the number of parts

1970s scifi/horror novel where protagonist is used by a crablike creature to feed its larvae, goes mad, and is defeated by retraumatising him

Reason why dimensional travelling would be restricted

CASE in WHERE clause in MySQL

Oracle SQL - CASE in a WHERE clauseDoes adherence to the DRY principle justify dynamic SQL?Mysql int vs varchar as primary key (InnoDB Storage Engine?How to write signal function in MySQL that can be called from Triggers and Stored Functions?How to optimize indexes on MySQL query with various sortsWhat's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)Update a date_modified MySQL Record Field ONLY when other fields have changed using CASE statement not working?UPDATE waits for lock too long after SELECT in MySQL (Innodb)Mysql trigger to update if date match and insert if no match all BEFORE INSERTPerformance difference between Text and Varchar in Mysql

0

I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$

CREATE PROCEDURE `case_in_where`(IN `column_index` INT, IN `value` VARCHAR(255)) 

    BEGIN



    SELECT * FROM `foo`

    WHERE 

        CASE

            WHEN `column_index` IS NULL THEN 1 

            WHEN `column_index` = 1 THEN `foo`.`column_1` = `value`

            WHEN `column_index` = 2 THEN `foo`.`column_2` = `value`

        END

    ;

END $$

DELIMITER ; 

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.

mysql performance stored-procedures case where

share

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$

CREATE PROCEDURE `case_in_where`(IN `column_index` INT, IN `value` VARCHAR(255)) 

    BEGIN



    SELECT * FROM `foo`

    WHERE 

        CASE

            WHEN `column_index` IS NULL THEN 1 

            WHEN `column_index` = 1 THEN `foo`.`column_1` = `value`

            WHEN `column_index` = 2 THEN `foo`.`column_2` = `value`

        END

    ;

END $$

DELIMITER ; 

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.

mysql performance stored-procedures case where

share

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$

CREATE PROCEDURE `case_in_where`(IN `column_index` INT, IN `value` VARCHAR(255)) 

    BEGIN



    SELECT * FROM `foo`

    WHERE 

        CASE

            WHEN `column_index` IS NULL THEN 1 

            WHEN `column_index` = 1 THEN `foo`.`column_1` = `value`

            WHEN `column_index` = 2 THEN `foo`.`column_2` = `value`

        END

    ;

END $$

DELIMITER ; 

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.

mysql performance stored-procedures case where

share

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

DELIMITER $$

CREATE PROCEDURE `case_in_where`(IN `column_index` INT, IN `value` VARCHAR(255)) 

    BEGIN



    SELECT * FROM `foo`

    WHERE 

        CASE

            WHEN `column_index` IS NULL THEN 1 

            WHEN `column_index` = 1 THEN `foo`.`column_1` = `value`

            WHEN `column_index` = 2 THEN `foo`.`column_2` = `value`

        END

    ;

END $$

DELIMITER ; 

share

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

Deus ArcanaDeus Arcana

11

New contributor

Deus Arcana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

Deus ArcanaDeus Arcana

11