By: Daniel Calbimonte | Comments (4) | Related: > Other Database Platforms
Problem
I have a need to work with both SQL Server and MariaDB. I need to know how to complete some common coding best practices. Can you provide a code comparison for MariaDB vs. SQL Server as it pertains to how to comment, how to create functions and procedures with parameters, how to store query results in a text file, how to show the top n rows in a query, how to use loops and more?
Solution
This tip demonstrates the code differences between SQL Server and MariaDB for a few common coding scenarios. Also check out my first tip that demonstrates how to Compare MariaDB vs SQL Server SQL Commands.
Requirements and Setup
- SQL Server 2014 or earlier versions.
- The MariaDB should be installed. In this tip, I installed in the same machine the MariaDB and the SQL Server in the Windows OS. You can find the MariaDB installer here.
Code Comparison for SQL Server vs. MariaDB
How to comment 1 line of code in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
--This is a comment for 1 in SQL Server line |
#This is the way to comment 1 line in MariaDB |
How to comment multiple lines of code in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
/*This is a comment in |
/*MariaDB is equal to |
How to SELECT the TOP 5 Rows in a table in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
SELECT TOP 5 * |
SELECT from customer |
How to create a function in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
CREATE FUNCTION dbo.hello_mssqltips() AS |
DELIMITER $$ |
How to create a function with parameters in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
CREATE FUNCTION dbo.hello_withparameter(@name varchar(30)) AS |
DELIMITER $$ |
How to call a function with parameters in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
select dbo.hello_withparameter('John') |
select hello_withparameter('John'); |
How to get information about a table in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
sp_help customer; |
describe customer; |
How to get the code definition in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
sp_helptext object_name Returns the code of existing procedures, functions and views. It is not applicable to tables. |
help create table help create procedure help create function |
How to create a procedure with a loop in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
create procedure dbo.repeatsample |
delimiter // |
How to execute a procedure with a loop in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
exec repeatsample 10 |
CALL repeatsample(10)// SELECT @value// Note that we are calling the @value variable later after iterating in the loop in the procedure. |
How to save the query results in a text file in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
Save this query in a file named customerquery.sql and save it at c:\. USE Test; Now at the cmd prompt run this command: sqlcmd -i c:\scripts\customerquery.sql -o c:\scripts\results.txt |
/*First update the user privileges, in this case the root User to increase the privileges to write files.*/ UPDATE user /*Now you can save the results of a query in the customer.csv file.*/ select * from customer |
How to use IF, ELSEIF and ELSE clauses in MariaDB vs. SQL Server
SQL Server
|
MariaDB |
---|---|
/*This sample shows the water state according to the temperature*/ CREATE FUNCTION dbo.WaterState(@temperature INT ) IF @temperature between 1 and 99 ELSE RETURN @state END |
DELIMITER // CREATE FUNCTION WaterState ( temperature INT ) BEGIN ELSEIF temperature between 1 and 99 then ELSE END IF; RETURN state; END; // DELIMITER ; |
Next Steps
For more information about T-SQL and Mariadb, refer to the following links:About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips