Posts Tagged ‘ MySQL ’

Introduction to MySQL Cross Tabb Query

There is nothing like “PIVOT” of SQL Sever 2005 and later versions in MySQL Database to accomplish Cross Tabb Quires. But it can be done using IF here. However, you can not execute Cross Tabb Query for unknown values in MySQL Server which is somehow possible in SQL Server. You can have a look at my posting on SQL Server Cross Tabb Quires. Let’s jump into coding to explore MySQL Cross Tabb query. Continue reading

Advertisements

MySQL Stored Function that returns Table

Don’t be misguided by the posting title. It’s not possible to write Table-Valued Stored Function in MySQL Server. MySQL Stored Function returns single values only. However, if the situation demands a Function to return a set of Data, you have only two choices.
Continue reading

Transaction with Stored Procedure in MySQL Server

Stored Procedure in MySQL Server is not atomic by default. To ensure atomicity, you need to write all your SQL Statements within START TRANSACTION .. COMMIIT Block. Moreover, you need to declare EXIT HANDLER for SQLEXCEPTION and SQLWARNINGS to ROLLBACK all of the SQL Statements in START TRANSACTION .. COMMIIT Block.


DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

Continue reading

Cursor with Stored Procedure in MySQL Server

Let’s start with Cursor directly. If you are not familiar with MySQL Stored Procedure, have a look at this posting first. Cursor is used to navigate thorough Result Set returned by a Query.

First of all, Cursor has to be declared. Cursor declaration is very simple.

DECLARE cursor_name CURSOR FOR select_statement;

Now, you need to use OPEN Command to start using this Cursor.

OPEN cursor_name;
Continue reading

Stored Procedure in MySQL Server

Stored Procedure is a collection of SQL commands and this is not executed until or unless called but compiled at the time of creation. Thus, this helps to reduce execution time and increases Data Integrity as well. In MySQL Server, Stored Procedure accepts three types of argument.

1) IN
2) OUT
3) INOUT
Continue reading

MySQL Stored Procedure, Function Problems with PHPMyAdmin

I wasted almost a day trying to write a Stored Procedure in MySQL using PHPMyAdmin. The first lesson I learnt is it’s not a good idea to start writing your Stored Procedure or User Defined Function using PHPMyAdmin. Try to download any of the free MySQL GUI Tool; I assure you will have a lot easier living with MySQL Database.
Continue reading

SQL to Select Random Rows from Table

Selecting Random Rows from Application Layer is always a mess. After some googling, I find those SQL Commands to SELECT Random Rows in different Databases. Here, I just put those Commands together for convenience.


-- SQL Server
SELECT TOP 1 * FROM TABLE_NAME
ORDER BY NEWID()

Continue reading