New to HeidiSQL - DECLARE keyword not recognising

Hi, I am new to HeidiSQL. I am moving my scripts from MSSQL to HeidiSQL and DECLARE is not being recognized in my version - 8.0.0.4396. Please help...

Thanks

I am using it MariaDB.

Do you have a question regarding HeidiSQL or about migrating your SQL code from SQL Server to MariaDB?

- If your script is not valid SQL there's nothing that HeidiSQL can do about it.

- If your script is valid and runs flawlessly from the command-line, they you'd you need to provide some info (the bare minimum: code, expected output, actual output).

If you move an SQL script from MSSQL to MariaDB and expect it to work there: good luck; the systems are very different.
(Especially true for triggers and stored routines.)

Thanks for trying to help me. I am trying to write a basic procedure to use a cursor. I am attaching a screen shot,

In the screen shot you will see that heidisql doesn't treat declare or cursor as keywords.

BEGIN
DECLARE 'crs' cursor for
select itemnumber from salesorder;
END

You've tried to link a picture from your computer:

C:\Users\DMS1\Desktop\capture.png

It looks like you are talking about the syntax highlighter. That'd be a cosmetic issue but it doesn't happen in my copy of HeidiSQL: DECLARE shows up bold and blue :-?

Looks like I also have the same problem if I understand it correctly, on my HeidiSQL too the keywords DECLARE or CURSOR does not show up in bold and blue which results into compilation error/syntax error of the stored procedure.

@kalvaro do we have to change any default settings on HeidiSQL?

Syntax errors are triggered by MySQL Server, not HeidiSQL. If you get them, it probably means that your code is not valid. The syntax highlighting is just a cosmetic feature that doesn't affect code parsing or execution.

If you provide further details (a code sample and instructions on what you do with it in HeidiSQL) we can determine if everything's correct on HeidiSQL side.

MSSQL and MySQL connections activate a slightly different syntax highlighting. But in both, DECLARE is bold, only in MySQL it's black and in MSSQL it's blue. Same with CURSOR.

However, anil803, do you get any error when executing your procedure? If yes, please paste the error message here.

Here my MSSQL script

DECLARE @Cursor2 CURSOR 
DECLARE @itemNum varchar(50)
SET @Cursor2 = CURSOR FAST_FORWARD 
FOR 
Select ItemNumber 
From @Result 
OPEN @Cursor2 
FETCH NEXT FROM @Cursor2 
INTO @itemNum
WHILE @@FETCH_STATUS = 0 
BEGIN 
FETCH NEXT FROM @Cursor2 
INTO @itemNum
END 
CLOSE @Cursor2 
DEALLOCATE @Cursor2

I am trying to convert this into MariaDB, so I tried as shown below

DECLARE tempCursor2 CURSOR 
FOR 
Select ItemNumber 
From tt_Result; 
OPEN tempCursor2 
FETCH NEXT FROM tempCursor2 
INTO itemNum
WHILE @@FETCH_STATUS = 0 
BEGIN 
FETCH NEXT FROM tempCursor2 
INTO itemNum
END 
CLOSE tempCursor2 
DEALLOCATE tempCursor2

however, my stored procedure does not save, it throws me the below 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 'DECLARE tempCursor2 CURSOR
FOR
Select ItemNumber
From tt_Result;

Thanks for help

Alright, I believe I mistakenly typed DECLARE into a SQL Server query tab. It's actually bold and black as Anse says.

Whatever, I understand you're using the "Create new/ Stored routine" feature. When you open the store route editor you get a default body that contains this:

BEGIN
END

You're expected to type your code inside. Otherwise, it isn't a valid procedure body.

I too have the same problem. I am copying the examples out of the MariaDB Crash Course book and am getting this error.

QL 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 '' at line 12.
(FYI: Line 12 is the line where the first DECLARE statement is)

I have looked SQL Keywords dropdown in Heidi, and DECLARE is not in that list. It goes from DEC to DEFAULT.

I am using the same version as above. My code is below:

#Name : ordertotal
#Parameters: onumber = order number
#taxable = 0 if not taxable, 1 if taxable
#ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
# Declare variable for total
DECLARE total DECIMAL(8,2);
# Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
# Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
#
# Is THIS TAXABLE?
IF taxable THEN
# Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
#
# And finally, save to out variable
SELECT total INTO ototal;
END;

Look at line 12. There is a semicolon, which is normally used as delimiter. In HeidiSQL, you can either a) use the routine editor to create a procedure, or b) use the query tab. In the latter case, you must set a delimiter, by clicking the button on the main toolbar with the red semicolon on it, and then use that instead of semicolon in your SQL code.

Assumig you set the delimiter to //, you need the following code:

#Name : ordertotal
#Parameters: onumber = order number
#taxable = 0 if not taxable, 1 if taxable
#ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
# Declare variable for total
DECLARE total DECIMAL(8,2)//
# Declare tax percentage
DECLARE taxrate INT DEFAULT 6//
# Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total//
#
# Is THIS TAXABLE?
IF taxable THEN
# Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total//
END IF//
#
# And finally, save to out variable
SELECT total INTO ototal//
END;

Thank you for your quick reply. Works like a charm.