First of all we have to crate our example table called “Customer”.
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!
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.
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.
SELECT *, mysql.getCustomerFullName(c.CustomerID) AS 'Full Name' FROM mysql.Customer AS c
This is how our output will look.
Please give some difference between functions and procedures.
please tell If i can return multiple values using a function, say all fields of a comment table join to all fields of user table.
other way in the line 5 of the function.
CREATE DEFINER=
scott@%FUNCTION
mysql.getCustomerFullName(intCustomerID INT)RETURNS varchar(100) CHARSET latin1
BEGIN
DECLARE strCustomerFullName VARCHAR(100);
SET strCustomerFullName = (SELECT CONCAT(FirstName, ‘ ‘, LastName) FROM Customer WHERE CustomerID = intCustomerID);
return strCustomerFullName;
END
Thanks Gustavo, your query is more efficient than mine.