SEO

#1 (**permalink**) 02-28-2008, 08:14 AM

Hi All,

Got a best practices question....

I often have to write PROCEDURES that return a value (because it needs
to have a transaction in it)...and I can't do that in a FUNCTION.

eg

CREATE PROCEDURE `sp_test01`()
BEGIN

START TRANSACTION;

-- Do some insert

COMMIT;

SELECT 1;

END$$

This makes it very easy to get a return value from the CALL sp_test01()
from, for example, C#....set it up.....ExecuteScalar.

However, it doesn't allow me to call this and get a return value from
within another PROCEDURE.

eg.

CALL sp_test01() INTO var_X;

now I know I can use an OUT parameter for the procedure, but coding for
that in C# is a pain. Most specifically its that in order to get the
OUT parameter ALL the parameters HAVE to be defined EXACTLY as they are
in the stored procedure.

So is there another way I can do this?

Regards

D.

#2 (**permalink**) 02-28-2008, 08:15 AM

daniel@mfaconsulting.com wrote:
> Hi All,
>
> Got a best practices question....
>
> I often have to write PROCEDURES that return a value (because it needs
> to have a transaction in it)...and I can't do that in a FUNCTION.
>
> eg
>
> CREATE PROCEDURE `sp_test01`()
> BEGIN
>
> START TRANSACTION;
>
> -- Do some insert
>
> COMMIT;
>
> SELECT 1;
>
> END$$
>
> This makes it very easy to get a return value from the CALL sp_test01()
> from, for example, C#....set it up.....ExecuteScalar.
>
> However, it doesn't allow me to call this and get a return value from
> within another PROCEDURE.
>
> eg.
>
> CALL sp_test01() INTO var_X;
>
> now I know I can use an OUT parameter for the procedure, but coding for
> that in C# is a pain. Most specifically its that in order to get the
> OUT parameter ALL the parameters HAVE to be defined EXACTLY as they are
> in the stored procedure.
>
> So is there another way I can do this?
>
> Regards
>
> D.
>

Standard SQL indicates you must specify the IN and OUT parameters. You
may think it's a pain - but that's how it works.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

HELP: FUNCTION and PROCEDURE best practices....