IS NOT NULL AS ... - Convert Query from MySQL to MS-SQL

Question

I have an application that is working fine using a MySQL/MariaDB-Database.
I did make it more flexible and now I am basically able to use a Microsoft SQL-Server database.

I found out, that some SQL-queries do NOT work anymore.
I don't have experience with MS-SQL and I am looking for support to convert the following query to make it work with MS-SQL. It would be great, if the query could be converted to work in both MS-SQL and MySQL ...

I have created an SQL-Fiddle with some example-data.
Link: http://sqlfiddle.com/#!18/5fb718/2

The Query itself looks like this:

SELECT computermapping.PrinterGUID, computerdefaultprinter.PrinterGUID IS NOT NULL AS isDefaultPrinter
FROM computermapping 
LEFT JOIN computerdefaultprinter ON computerdefaultprinter.ComputerGUID = computermapping.ComputerGUID 
AND computerdefaultprinter.PrinterGUID = computermapping.PrinterGUID
WHERE computermapping.ComputerGUID = "5bec3779-b002-46ba-97c4-19158c13001f"

When I run this on SQL-Fiddle I get the following error:

Incorrect syntax near the keyword 'IS'.

When I run this Query in Microsoft SQL Server Management Studio I get the same Error. I have an German-Installation ...

Meldung 156, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe des IS-Schlüsselworts.

I was looking on the Internet to find information on how to use the IS NOT NULL AS in MS-SQL. Maybe I was using the wrong keywords, but I was not able to find a solution myself.

If it does matter, I am using "SQL-Server 2014 SP3" at the moment.

Thank you

Each database has its own SQL dialect and MySQL is one of the quirkiest. ALL databases are quirky, none follows the SQL standard beyond a basic compatibility level. MySQL is one of the worst offenders, with many common features added in MySQL 8. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 8, 2021 at 8:56
Aside: Try select 30 / -3 / 5; in MySql and SQL Server. Not as portable as one might hope. — HABO
– HABO, Commented Jan 11, 2021 at 16:30

Giorgos Betsos · Accepted Answer · 2021-01-08 08:30:42Z

3

Convert

computerdefaultprinter.PrinterGUID IS NOT NULL AS isDefaultPrinter

to

CASE 
   WHEN computerdefaultprinter.PrinterGUID IS NOT NULL THEN 1 
   ELSE 0 
END AS isDefaultPrinter

Demo here

Also bear in mind that there is no BOOLEAN type in SQL Server. BIT type is used instead.

Finally

WHERE computermapping.ComputerGUID = "5bec3779-b002-46ba-97c4-19158c13001f"

should be converted to

WHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

since the single quote character is used to delimit strings in SQL Server

edited Jan 8, 2021 at 8:30

answered Jan 8, 2021 at 8:23

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

4 Comments

Zhorov Over a year ago

Correct, but WHERE computermapping.ComputerGUID = "5bec3779-b002-46ba-97c4-19158c13001f" is also an issue. +1

Giorgos Betsos Over a year ago

@Zhorov Yes, this is correct. I've only fixed this in the demo attached, since the OP doesn't mention the issue.

Walhalla Over a year ago

Wow .. that was fast. I am starting a short test with production data.

Walhalla Over a year ago

Could you please tell me more about the issue with the WHERE-clause?

forpas · Accepted Answer · 2021-01-08 08:25:17Z

2

MySql evaluates boolean expressions like:

computerdefaultprinter.PrinterGUID IS NOT NULL

as 1 for TRUE or 0 for FALSE.
SQL Server does not do such an evaluation, so you need a CASE expression:

CASE WHEN computerdefaultprinter.PrinterGUID IS NOT NULL THEN 1 ELSE 0 END

answered Jan 8, 2021 at 8:25

forpas

165k10 gold badges51 silver badges85 bronze badges

Comments

Panagiotis Kanavos · Accepted Answer · 2021-01-08 09:10:57Z

The exact question was already answered, but you should really, really try using the SQL Server Migration Assistant for MySQL. No database supports the SQL standard beyond a basic compatibility level and MySQL is one of the quirkiest databases when it comes to SQL compatibility.

The SQL standard process is sloooow so all vendors implement features long before they're standardized. Different databases have different priorities too, and MySQL's priority for the first decade at least wasn't enterprise applications, so SQL compatibility wasn't a high priority.

Some common features were added only in MySQL 8. Some hacks allowed (but discouraged) in MySQL, like non-aggregate columns in a grouping query, or quirky updates to calculate row numbers, don't work in any other databases because logically, they lead to unpredictable results.

Even in MySQL, non-aggregate columns can cause serious performance degradation when upgrading from one 5.x version to the next. There's at least one SO question about this.

The Migration assistant will convert tables and types where possible and flag any stored procedure or view queries that can't be translated. It can also copy data from an existing MySQL database to a SQL Server database

Collectives™ on Stack Overflow

IS NOT NULL AS ... - Convert Query from MySQL to MS-SQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related