|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `GetAllFiles`( IN sort VARCHAR(4) ) BEGIN SET @orderBy = sort; SET @sqlStatement = concat(' SELECT id, name, path, type, size, ispublic, code, insertdate FROM File ORDER BY insertdate' + @orderBy + '; '); PREPARE statement FROM @sqlStatement; EXECUTE statement; DEALLOCATE PREPARE statement; END |
Category Archives: MySQL
MySQL Create Function Example
First of all we have to crate our example table called “Customer”.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `mysql`.`Customer` ( `CustomerID` int(11) NOT NULL auto_increment, `CompanyID` int(11) NOT NULL, `FirstName` varchar(45) default NULL, `LastName` varchar(45) default NULL, `Address` varchar(500) default NULL, `City` varchar(45) default NULL, `State` varchar(45) default NULL, `ZipCode` int(5) default NULL, `ContactNumber` int(10) default NULL, PRIMARY KEY (`CustomerID`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 |
We have to insert one record into Customer table. Let’s add Steve Jobs! 🙂
|
1 2 |
INSERT INTO mysql.Customer(CompanyID, FirstName, LastName, Address, City, State, ZipCode, ContactNumber) VALUES(1, 'Steve', 'Jobs', '1 Infinity Loop', 'Cupertino', 'CA', 95014, 1112223333); |
Here is syntax to create our function which will bring Concat First and Last Name and return single string.
|
1 2 3 4 5 6 7 8 |
CREATE DEFINER=`scott`@`%` FUNCTION `mysql`.`getCustomerFullName`(intCustomerID INT) RETURNS varchar(100) CHARSET latin1 BEGIN DECLARE strCustomerFullName VARCHAR(100); SET strCustomerFullName = CONCAT((SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),' ', (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID)); return strCustomerFullName; END |
How to call getCustomerFullName() function.
|
1 |
SELECT *, mysql.getCustomerFullName(c.CustomerID) AS 'Full Name' FROM mysql.Customer AS c |
This is how our output will look.
