CODEWITHSUNDEEP
c#mysqlsql-server
pStan
pStan

Reputation: 1104

mysql stored procedure parameters don't seem to work with "@" (At sign)

I'm changing a MS SQL database over to MySQL.

I've rewritten one of the stored procedures which takes a parameter called @Account_Number. When I run the stored procedure in MySQL I get the following Message: Error Code: 1048 Column 'Account_Number' cannot be null.

Using the workbench I finally tinkered around and figured out that when I removed the "@" from my stored procedures and renamed them like parmAccount_Number the stored procedure would execute.

I really want to keep the stored procedure input parameters named the same, and I don't want to go back and rename my MS SQL parameters... just in case I want to flip flop Databases.

I can't find any info on what MySQL does with the "@"....

Is there a way to make it work with "@" ?

EDIT

Declaring the stored procedure 

CREATE PROCEDURE `My_sp`(
 `@Account_Number` varchar(8),....)

insert portion of sp

insert into 
My_Table
(
    Account_Number, ...
)
values
(
    @Account_Number,...
)

Upvotes: 3

Views: 1617

Answers (1)

spencer7593
spencer7593

Reputation: 108460

No, you can't name MySQL procedure variables starting with the @ sign.

The @ sign character signifies a MySQL User-Defined Variable, which is quite different than a procedure variable.

The big differences is that user defined variable aren't declared (they don't have a datatype), and they have scope within a session (database connection), not just a procedure. It persists for the entire session. (This also means it's subject to modification anywhere in the session, any SQL statement, trigger, function or procedure can modify it, which makes it very useful, and also makes it a potential pitfall.

It's possible to use user defined variables as the actual values passed to a procedure or function. But you can't name MySQL procedure variables or parameters starting with an @ sign.

I believe this is compliant with the SQL 2003 standards.

Documentation here:

http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

UPDATE

Interesting.

Given that you are able to get a procedure compiled by enclosing the parameter name in backticks (as shown in the example statement you added)...

You might try enclosing the parameter name reference in the INSERT statement in backticks as well. (I've never tested that; I've never tried "back ticking" variable names before; I've never seen that done before.) But if the procedure is actually compiling, then maybe that would work.

Upvotes: 2

Related Questions