3

I've been migrating MSSQL DB into MySQL and facing a syntax I cannot find anywhere and need this help. All "MySQL parameters" question deals with "select * from mytable where id = @id" but I'm looking for the below syntax:

SELECT @MyParam = MyValue FROM MyTable

While "SELECT MyValue FROM MyTable" returns values, running the above @MyParam query returns NULLs.

From previous answers I see that as opposed to MSSQL, in MySQL the parameters aren't predefined with any datatype.

What am I missing here?

THX

Improve this question

2 Answers 2

Reset to default
2

You need to use := operator instead of =, when you are assigning value to a user-defined variable in the SELECT clause. Former is assignment operator; while the latter is comparison operator.

SELECT @MyParam := MyValue FROM MyTable

However, if you are using SET clause, then you can use either = or :=. From docs:

For SET, either = or := can be used as the assignment operator.

More from the docs:

When making an assignment in this way (using SELECT), you must use := as the assignment operator; = is treated as the comparison operator in statements other than SET.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Excellent! That did the trick. I should have thought on this (though I'm MSSQL fan forced to move to MySQL..) Thx so much.
The := format with SELCECT will cause extra result set from the procedure. Use SELECT col INTO vVariable syntax instead.
1

When converting MS SQL Server procedures to MySQL procedures, you better use local variables declared with DECLARE.

The @var variables are user-defined variable and the scope is the process. So if your procedures call other procedures, the variable value may change whereas local variables will not. Local variables also have a defined type.

To convert MS SQL Server syntax 

SELECT @MyParam = MyValue FROM MyTable

into MySQL, usee:

DECLARE vMyParam int;
SELECT MyValue INTO vMyParam FROM MyTable;

The format:

SELECT @MyParam := MyValue FROM MyTable

will cause an extra result set from the procedure so that is probably something you do not want.

You can use the := format when you use SET-command where you explicitly set a value for a variable.

SET vMyParam = 10;
Improve this answer

1 Comment

thanks for clarifying this. Just tested it and it worked perfectly. Thx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.