Syntax error?

[Joneleth]

Hi,

I'm developing an ASP.NET web application with MySql 5.
I have a little problem: if i create a stored procedure like this one:

CREATE PROCEDURE `spProva` (out retval int, in idemployee int)
BEGIN
UPDATE employee e SET e.Room = 'Milan 41' WHERE e.IdEmployee =
idemployee;
SET retval = idemployee;
END

everything works fine, while if I use a textual query programmatically
created:

string sqlText = "UPDATE employee e SET e.Room = ?Room
WHERE e.IdEmployee=?IdEmployee; ";
sqlText += "SET ?retval = ?IdEmployee; ";

with ?Room, ?IdEmployee and ?retval presetted parameters, an error
raises: #42000You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '0 = 55'.

It seems that the expression is pre-evaluated and the variables
replaced before executing the query, and so an istruction like 'SET 0 =
55' clearly has no sense.
Is there a way to work around that issue, without using stored
procedure?

Thanks.

[Bill Karwin]

"Joneleth" <Joneleth_76@hotmail.com> wrote in message
news:1139932173.725034.108500@f14g2000cwb.googlegr oups.com...

> string sqlText = "UPDATE employee e SET e.Room = ?Room
> WHERE e.IdEmployee=?IdEmployee; ";
> sqlText += "SET ?retval = ?IdEmployee; ";

....

> Is there a way to work around that issue, without using stored
> procedure?

I've never used Visual Basic. Can you tell me what the second line is
intended to do? I assume it's copying the value of one VB variable to
another VB variable. Why make MySQL do that? Can't you just assign one
variable's value to another like this:
retval = IdEmployee;

It also seems to me that you might not need the retval variable at all,
since its purpose in the stored procedure example is just to return a value
from the procedure; this is not relevant if you're not using a stored
procedure. In fact, its purpose in the stored procedure example is not
clear, since it just returns the value that you input when you called the
procedure.

Regards,
Bill K.

[Joneleth]

Bill Karwin wrote:

> > Is there a way to work around that issue, without using stored
> > procedure?

>
> I've never used Visual Basic.

It's not VB but C# :)

> It also seems to me that you might not need the retval variable at all,

In the case of that small example you're right, the retval is quite
useless.
However, if you're going to use an insert statement instead of an
update, for example,
you might need to retrieve the last inserted ID in the same transaction
to avoid potential (concurrency) errors.
Since I'm used to write query with SqlServer, I've never had such a
problem, even using textual query in place of stored procedures.

> In fact, its purpose in the stored procedure example is not
> clear, since it just returns the value that you input when you called the
> procedure.

yeah, it was just an example :)

Regards,
J.