CODEWITHSUNDEEP
mysqlparameters
Dave Gahan
Dave Gahan

Reputation: 299

How to use SELECT parameters assignments in MySQL queries?

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

Upvotes: 3

Views: 2112

Answers (2)

slaakso
slaakso

Reputation: 9050

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;

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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.

Upvotes: 2

Related Questions