Creating a stored procedure in heidisql

mehakj's profile image mehakj posted 4 years ago in General Permalink

I am using heidisql, version 10.2.0.5599. I have a database and I want to create a stored procedure in that. I am trying to create it using Create new Stored Routine menu option, as in the below screen-print,

Description

but I get the error, SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN SELECT FROM 'ca_payment_run'; END' at line 8 /

Could anyone tell me where am I wrong ?

ansgar's profile image ansgar posted 4 years ago Permalink

On which server (version)?

mehakj's profile image mehakj posted 4 years ago Permalink

MariaDB. Version 10.4.8

ansgar's profile image ansgar posted 4 years ago Permalink

I get no error, with the same stuff entered as I see in your screenshot. So there must be some more stuff I cannot see, probably a parameter. Please post the code from the CREATE code tab here.

mehakj's profile image mehakj posted 4 years ago Permalink

The screen-print of the CREATE code tab is attached below-

Description

ansgar's profile image ansgar posted 4 years ago Permalink

Please paste that as text here, so I don't have to type the code from your screenshot...

mehakj's profile image mehakj posted 4 years ago Permalink

Below is text-

CREATE PROCEDURE insertPaymentRun() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'a' BEGIN select * from ca_payment_run; END

ansgar's profile image ansgar posted 4 years ago Permalink

If I set the delimiter to something different than semicolon, this even works in a query tab of HeidiSQL:

DELIMITER \\
CREATE PROCEDURE `insertPaymentRun2`()
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT 'a'
  BEGIN select * from mytable; END

You must be doing something different than I do.

mehakj's profile image mehakj posted 4 years ago Permalink

Running this also doesn't create the procedure.

To create the procedure using CREATE new option, I click on the database and then select Stored routine from Create new option. Then in the fields, I enter all the values, mentioned in the first screen-print attached above. But I get the syntax error.

If I try to create using a query, then it gives no results and the procedure is not created.

What could be missing at my end ?

ansgar's profile image ansgar posted 4 years ago Permalink

Hm, you could compare the generated CREATE PROCEDURE command from your SQL log panel to what I wrote in my previous post. Maybe there is some difference.

mehakj's profile image mehakj posted 4 years ago Permalink

I tried it but I did not find any difference.

ansgar's profile image ansgar posted 4 years ago Permalink

If they don't have any difference, I can't believe the one gives you an error, and the other one not so.

Maybe you should copy the generated SQL from the log panel, the line right before the error occurs. Then, post this CREATE code here.

Please login to leave a reply, or register at first.