SQL Hacks

Chapter 8. Storing Small Amounts of Data

If you are accustomed to a programming language such as Perl, Java, or Visual Basic, you will have grown used to having variables to store temporary values, global constants, and parameters that are known only at runtime. It would be convenient to have similar variables in SQL. If you want to perform 10 different actions on “Jim Smith,” for example, it’s a shame to have to say “Jim Smith” in 10 different queries. Some queries, especially ones involving subqueries, reuse the same search conditions multiple times, and again it would be good not to have to say “Jim Smith” multiple times in the same query. If variables were available in SQL, you could use them to parameterize your code. However, the SQL standard has no obvious support for named variables to store such small values.

Most vendors supply a simple programming language with their SQL engine, which generally include support for variables. But you don’t have to rely on that. You can stick to minimal SQL and use a single-row table [Hack #58] to keep variables. There are costs, however. Your single-row table will likely be kept on disk and will be slower to access than traditional memory-resident variables. Query optimization and caching technologies can minimize this cost.

With a little more work you can share a singleton style among multiple users [Hack #59], allowing each user to have variables without one user’s variables interfering with those of a different user.

Store Parameters in ...