Re: creating view with user variable
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Fri, 27 Jan 2017 08:38:56 -0500
Message-ID: <o6fie2$rmm$1_at_jstuckle.eternal-september.org>
>
>
> Dynamic SQL is no less (or more) efficient than generating the SQL on the
> client and sending it as static string to MySQL. In the end it takes the
> same way through SQL parser and executer. The PREPARE + EXECUTE steps are
> just necessary to emulate the missing EXECUTE IMMEDIATE functionality.
>
> There are other tasks, where dynamic SQL is very handy and elegant, i.e.
> when it comes to using results from INFORMATION_SCHEMA to build maintenance
> queries. Imagine a procedure that finds all tables in the `foo` database
> using the MyISAM engine and running OPTIMIZE TABLE on them.
>
>
> XL
>
Date: Fri, 27 Jan 2017 08:38:56 -0500
Message-ID: <o6fie2$rmm$1_at_jstuckle.eternal-september.org>
On 1/27/2017 4:57 AM, Axel Schwenke wrote:
> On 26.01.2017 21:48, Jerry Stuckle wrote:
>> Yes, you can use a prepared statement for that. Much less efficient, >> though and I tend to stay away from it.
>
>
> Dynamic SQL is no less (or more) efficient than generating the SQL on the
> client and sending it as static string to MySQL. In the end it takes the
> same way through SQL parser and executer. The PREPARE + EXECUTE steps are
> just necessary to emulate the missing EXECUTE IMMEDIATE functionality.
>
> There are other tasks, where dynamic SQL is very handy and elegant, i.e.
> when it comes to using results from INFORMATION_SCHEMA to build maintenance
> queries. Imagine a procedure that finds all tables in the `foo` database
> using the MyISAM engine and running OPTIMIZE TABLE on them.
>
>
> XL
>
That's true in MySQL, but not in some other databases such as DB2. But in this case the SQL is in a stored procedure in the server, so your comment is immaterial.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Fri Jan 27 2017 - 14:38:56 CET