SingleStore Stored Procedures

sproc-delimiter.sql

CREATE DATABASE IF NOT EXISTS acme;

USE acme;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  content varchar(300) NOT NULL,
  createdate TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO messages (
  content
) VALUES (
  'building stored procedures'
);

drop procedure if exists messages_update;

-- Switch the delimiter so we can use `;` inside the stored procedure
DELIMITER //
CREATE OR REPLACE PROCEDURE messages_update(_id BIGINT, _content VARCHAR(300)) AS
BEGIN
  update messages m set m.content = _content where m.id = _id;
END //
-- And set it back when we're done
DELIMITER ;


call messages_update(1, 'updated via stored procedure');