I am migrating many MySQL queries from being hard-coded in a Node.js app to stored procedures (for use in a Java app). In the original Node app there is one SQL condition saved as a constant string that is substituted into many different SQL functions as part of the where clause and I cannot figure out how to accomplish the same thing using MySQL stored functions.
The constant is a SQL string that checks whether a variety of properties are null on a user object:
const USER_INFO_NOT_NULL = `u.id IS NOT NULL \
AND u.email IS NOT NULL \
AND u.gender IS NOT NULL \
AND u.date_of_birth IS NOT NULL \
AND u.first_name IS NOT NULL \
AND u.last_name IS NOT NULL`
This SQL statement is substituted into many different queries as one of the conditions in their WHERE
statement.
For example:
SELECT COUNT(*) count
FROM User u
WHERE ${USER_INFO_NOT_NULL}
AND some_other_condition
AND another_condition
...
Unfortunately, not all of the queries are as simple as the one above and it would be a lot of work to rewrite each one.
I've tried using a stored function, however, stored functions can only take scalar values as parameters so I cannot pass along a user row.
I've also tried having the function take the user id (not ideal as the queries have already queried the user), query for that user and return a boolean based on the above condition. However, I'm not sure how to combine a SELECT
with a RETURN
in a stored function (can you do this?).
I've looked into prepared statements, however, they don't seem to work in this way as they only support a limited number of SQL commands.
So, is there a way to do something similar to what already exists in the Node.js app using only MySQL features?
You can define a view.
CREATE VIEW user_info_not_null
SELECT *
FROM User u
WHERE u.id IS NOT NULL
AND u.email IS NOT NULL
AND u.gender IS NOT NULL
AND u.date_of_birth IS NOT NULL
AND u.first_name IS NOT NULL
AND u.last_name IS NOT NULL;
Then you can use the view like a table:
SELECT COUNT(*) count
FROM user_info_not_null
WHERE some_other_condition
AND another_condition
Implementing follower system using a many to many field through a model
IBM Watson Assistant Importing error “should NOT be shorter than 1 characters.”
How can I extract values from a dataframe or filter based on some criteria in Python?
Opencart 3.0.3.2 - Grabbing Multidimensional Array on Ajax + Twig
Is there a way to create a new php file for every sql entry?
This question already has an answer here:
I have to write a regex in SQL to filter and capture the file name and exclude the date stamps and the file fromat, is there any way we can do that? the examples I have are-
I need to emulate an Intersect query in MySQL
I have created a database using phpmyadmin called test that has one table called client_infoThe table in that database is empty (as shown in the attached image)