What was that ?
Yes.. I mean use variables in SQL. This can be quite interesting, though there may be a lot of people who might comment, that this kills readability, or does not induce even a ray of benefit, well I am not into an argument. And the method I am testing only with MySQL. In several situations, it has proven to be more faster, and as of recent times, we have started to send the whole of the code as a single operation letting mysql to handle the query splitting.
Suppose we need to insert first into a login table, to generate the user_id, which is auto-increment, and then into a set of tables, normally, we would go around insert into the table, then use mysql_insert_id(), to get the last inserted id, to process the rest of the tables. Now think if we used
insert into [table1] (fieldnames) values (values);
select last_insert_id() into @ii;
insert into [table2] (fkId,fieldnames) values (@ii,values);
insert into [table3] (fkId,fieldnames) values (@ii,values);
There are several other example uses for selecting into a variable, but the limitations are that you can select only a single field and single row.
Any comments :