Converting SQL Server Stored Proc to MySQL Stored Proc

[Steve Bell]

I'm trying to convert the following SQL Server Stored Proc to a MySQL equivalent. The problem I'm having is the conversion of the statement (OUTPUT inserted.customerReference INTO @MyCustomer) to populate a temporary table I've created to hold the customer reference of the row(s) I've updated so I can return it to the function calling the SP.

Any advice would be appreciated.

Steve


USE [UKAutomation]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Steve Bell
-- Create date: 06/07/15
-- Description: Allocate all demands for a customer to a user based on skill set and chronological
-- order of createdDateTime
-- =============================================
CREATE PROCEDURE [CM].[SPALLOCATECUSTOMERTOUSER]
@pinuserId varchar(15),
@pinstatus int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Define temporary table to hold the customer reference of
-- the customer being allocated to a user
DECLARE @MyCustomer table(customerReference varchar(20))

-- Update row (or rows where a customer has multiple demands) with the user's userid
-- where the demand has not been allocated. The demand is allocated based on user's skill set and chronological order
UPDATE CM.GenericDemandData
SET userId = @pinuserId, status = @pinstatus
OUTPUT inserted.customerReference INTO @MyCustomer
WHERE status = 0
AND customerReference = (SELECT top 1 gdd.customerReference
from CM.GenericDemandData gdd
left outer join CM.UserSkills us on us.demandTypeId = gdd.demandTypeId
and us.userid = @pinuserId
where gdd.status = 0
group by gdd.customerReference
having count(*) = count(us.userid)
order by min(gdd.createdDateTime))

IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN 1
END

-- Retrieve details of the customer that has been allocated to the user along with the
-- id of the row from the generic demand table that relates to the demand raised by the customer
-- In the event the customer has multiple demands then the id of the most recent one will be returned
SELECT ph.policyholderId, ph.forenames, ph.lastName
FROM CM.PolicyholderData ph, CM.GenericDemandData gdd
WHERE ph.customerReference = (SELECT DISTINCT(customerReference)
FROM @MyCustomer)
AND gdd.customerReference = ph.customerReference
GROUP BY ph.policyholderId, ph.forenames, ph.lastName

END





GO