Do you need the retval for anything? I would say no (unless this is a
constraint in sprocs in MySQL).
Think about it this way:
1. Pass in employeeID = 1
2. UPDATE record
3. Pass out retval = 1
This never changes. You do not need this value, as the only way it can be
anything different is in case of error, which will throw an error and not
return anything (unless MySQL is a really strange RDBMS these days ;->).
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
[quoted text, click to view] "Joneleth" wrote:
> 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 instruction like 'SET 0
> =
> 55' clearly has no sense.
> Is there any way to work around that issue, without using stored
> procedure?
>
> Thanks.
>
Did you set the CommandType to stored procedure before executing?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
[quoted text, click to view] "Joneleth" <Joneleth_76@hotmail.com> wrote in message
news:1139932811.148978.224930@o13g2000cwo.googlegroups.com...
> 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 instruction like 'SET 0
> =
> 55' clearly has no sense.
> Is there any way to work around that issue, without using stored
> procedure?
>
> Thanks.
>
[quoted text, click to view] Cowboy (Gregory A. Beamer) - MVP wrote:
> This never changes. You do not need this value, as the only way it can be
> anything different is in case of error, which will throw an error and not
> return anything (unless MySQL is a really strange RDBMS these days ;->).
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 secure
transaction
(not command.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.
Regards,
J.
