Requesting some SQL Assistance...

[Mel Riffe]

Just when I thought I could leave SQL in my rear-view mirror... ;-)

I'm trying to write a stored procedure. The overall goal is to select some data, throw an error if no data found, else create a new record from the data.

This stored procedure takes 5 input parameters: IN claim_id INTEGER, IN auditable_type VARCHAR(255), IN auditable_id INTEGER UNSIGNED, IN error_code VARCHAR(255), IN claim_status VARCHAR(255). Typical calling would be like: call sp_record_audit_error(1, 'Claim', 1, '600', 'I');

I'm trying to do something like the following:

  SELECT lae.error_message, lae.severity, lae.field_name INTO lae_error_message, lae_severity, lae_field_name FROM lookup_audit_errors lae WHERE error_code = error_code AND FIND_IN_SET(claim_status, status) > 0;

  IF lae_error_message IS NULL THEN

    SIGNAL lae_not_found

      SET MESSAGE_TEXT = 'Specified LookupAuditError record not found.';

  END IF;

  INSERT INTO audit_errors (claim_id, auditable_id, auditable_type, error_code, error_message, severity, field_name, created_at, updated_at) VALUES(claim_id, auditable_id, auditable_type, error_code, lae_error_message, lae_severity, lae_field_name, NOW(), NOW());

But I can't seem to get it to work 'as-is' or with prepared statements.

What am I doing wrong?

Thank you for your assistance and time!