So I am very new to MySQL but not MSSQL Server here is a simple sp that is a transaction with a try catch and system functions to return error information. I am looking for simple conversion to MySQL syntax. Can someone please translate so I can have a little template.
CREATE PROCEDURE [dbo].[spDELETE_TREATMENT]
@treatmentid as int
@errorMessage as varchar(1000) output,
@errorProcedure as varchar(1000) output,
@errorLine as int output,
@errorNumber as int output,
@errorSeverity int output,
@errorState as int output
– =============================================
– Author:
– Create date: <August 1, 2007>
– Description:
– Called from: <NewTreatmentForm.aspx.vb>
– =============================================
as
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
BEGIN TRY
–do some updates or deletes…
–raise an error to jump to catch block and set output variables
RAISERROR(‘xxxxx’,16,1)
COMMIT TRAN
SET @error = 0
SET @errorMessage = NULL
SET @errorProcedure = NULL
SET @errorLine = NULL
SEt @errorNumber = NULL
SET @errorSeverity = NULL
SET @errorState = NULL
RETURN @error
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @error = -100
SET @errorMessage = ERROR_MESSAGE() --MSSQL system function
SET @errorProcedure = ERROR_PROCEDURE()–MSSQL system function
SET @errorLine = ERROR_LINE() --MSSQL system function
SEt @errorNumber = ERROR_NUMBER() --MSSQL system function
SET @errorSeverity = ERROR_SEVERITY() --MSSQL system function
SET @errorState = ERROR_STATE() --MSSQL system function
RETURN @error
END CATCH
END