{question}
How to create stored procedure via SQL Workbench?
{question}
{answer}
There are several ways to connect to SingleStore using many popular clients. One such way to connect to SingleStore is using a popular IDE called SQL Workbench. Click here to learn more about the tool.
To learn about how to connect SingleStore Database using SQL Workbench, click here.
In this article, we will learn about creating stored procedures via SQL Workbench.
For creating stored procedures via SQL Workbench, It's required to learn about alternate delimiter specific to SQL Workbench. Click here to learn about alternate delimiter in SQL Workbench.
SQL Workbench will split statements based on the SQL terminator ; and send each statement unaltered to the DBMS.
When executing statements such as CREATE PROCEDURE which in turn contain valid SQL statements, delimited with a ; the SQL Workbench will send everything up to the first semicolon to the backend (because the ; terminates the SQL statement) In case of a CREATE PROCEDURE statement, this will result in an error as the statement is not complete.
To be able to run DDL statements with embedded ; characters, SQL Workbench, needs to know where a statement ends. To specify the end of a statement with embedded ; the so-called "alternate delimiter" is used.
Let's use the below create the procedure as an example,
DELIMITER //
CREATE OR REPLACE PROCEDURE testing.thisworks() AS
BEGIN
ECHO SELECT 1;
END //
DELIMITER ;
To run the above CREATE PROCEDURE statement via SQL Workbench. Connection Profile needed to be configured and then syntax changes as mentioned below,
Add the Alternate Delimiter setting as below:
Example Connection profile:
Before executing, create a stored procedure statement; as pointed below, add / in the Alternate Delimiter box and save the profile.
Syntax to be used via SQL Workbench:
Below is the syntax with ALTERNATE DELIMITER,
CREATE OR REPLACE PROCEDURE testing.thisworks() AS
BEGIN
ECHO SELECT 1;
END;
/
Example SQL Workbench Screenshot:
Note: SQL Workbench is a 3rd Party Utility. SingleStore doesn't manage this tool. To learn more about SQL Workbench, click here.
{answer}