03. MySQL example

Sample stored procedureGeneric Database

CREATE PROCEDURE `getData`(
    callerIdNum varchar(50),exten varchar(50),
    didNumber varchar(50), queueName varchar(50),
    v1 varchar(50), vw varchar(50),
    v3 varchar(50), v4 varchar(50))
BEGIN

SELECT   (account.name + ' outstanding balance ' + account.balance) as [DisplayData]
	    , account.accountId [PasteData]
	    , 'http://www.yourcrm.com.au/searchAccountID=' + account.accountId as[URL]
	    , contact.firstName + ' ' + contact.surname as [CallerName]
	    from [YourDatabaseName].[dbo].[tblContact] as contact
	    join [YourDatabaseName].[dbo].[tblAccount] as account
	        on contact.accountId = account.accountId
	    where contact.phoneNumber = callerIdNum;
END

Sample QueryGeneric Database

You can test your query by running the following commands from the mysql cli.

The following example assumes that you have a contact in your database with a phone number of '5551234'.

Note for this to work the phone number in your database must not contain spaces or other characters.

If your phone numbers do then you will need to have the above select statement strip them out.

use YourDatabaseName;
call getData('5551234','100','61383208100','support','','','','')