Mysql stored procedure check if value is null

There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. I have 6 optional arguments to a stored procedure that are used to filter a certain value of a query. The previous developer did something like this:. I didn't particularly like this. The query is quite massive , so I decided to opt for something like this: We had the results in a temp table anyway.

PHP MySQL: Call MySQL Stored Procedures

The only thing I can think to do is to create another temp table that will hold only the not-null values in var1, etc. Either using if statements or deleting where is NULL and then doing a join delete on this table.

If you can't use values to create a derived table you can use union all instead. By posting your answer, you agree to the privacy policy and terms of service.

Sign up or log in to customize your list.

Stack Exchange Inbox Reputation and Badges. Questions Tags Users Badges Unanswered. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top.

Best way to work around NOT IN NULL issue? The previous developer did something like this: IF var1 IS NULL AND We had the results in a temp table anyway IF var IS NOT NULL OR But I realized that this won't work.

Is there a better way? MikaelEriksson I think it's NOT IN that hes worried about.

MySQL Stored Procedures That Return Multiple Values

Is it an option to give these variables default values? If you give them a value you know will never be present, say an empty string, or or something, it should be fine to not change any other code.

I hate using magic numbers, though. I thought about that JNK, but that just seems silly..

Using MySQL Stored Procedures with PHP mysql/mysqli/pdo « Joey Rivera

Don't know about Sybase but in SQL Server you can do like this. Is there any performance repercussion to doing so many sub-queries? In SQL Server it gets treated as a derived table, so it only gets calculated once. The only time you get a row-by-row execution is if the outer table value is used in the inner table, normally. This should work the same. What you've already come up with by yourself: To understand NULLs better I recommend reading the Sybase documentation about set ansinull [ on off ] or a nice article by Robert Vollman about NULLs.

Sign up or log in StackExchange. Sign up using Facebook.

Common MySQL Queries

Sign up using Email and Password. Post as a guest Name. Database Administrators Stack Exchange works best with JavaScript enabled. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3.

Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.