What is Ad Hoc Query?

Question

Ad hoc is latin for "for this purpose". You might call it an "on the fly" query, or a "just so" query. It's the kind of SQL query you just loosely type out where you need it

var newSqlQuery = "SELECT * FROM table WHERE id = " + myId;

...which is an entirely different query each time that line of code is executed, depending on the value of myId. The opposite of an ad hoc query is a predefined query such as a Stored Procedure, where you have created a single query for the entire generalized purpose of selecting from that table (say), and pass the ID as a variable.

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.

Check: http://www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-an-ad-hoc-query.html

An Ad-hoc query is one created to provide a specific recordset from any or multiple merged tables available on the DB server. These queries usually serve a single-use purpose, and may not be necessary to incorporate into any stored procedure to run again in the future.

Ad-hoc scenario: You receive a request for a specific subset of data with a unique set of variables. If there is no pre-written query that can provide the necessary results, you must write an Ad-hoc query to generate the recordset results.

Beyond a single use Ad-hoc query are stored procedures; i.e. queries which are stored within the DB interface tool. These stored procedures can then be executed in sequence within a module or macro to accomplish a predefined task either on demand, on a schedule, or triggered by another event.

Stored Procedure scenario: Every month you need to generate a report from the same set of tables and with the same variables (these variables may be specific predefined values, computed values such as “end of current month”, or a user’s input values). You would created the procedure as an ad-hoc query the first time. After testing the results to ensure accuracy, you may chose to deploy this query. You would then store the query or series of queries in a module or macro to run again as needed.

Also want to add that ad hoc query is vulnerable to SQL injection attacks. We should try to avoid using it and use parameterized SQLs instead (like PreparedStatement in Java).

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools. An ad hoc query does not reside in the computer or the database manager but is dynamically created depending on the needs of the data user.

In SQL, an ad hoc query is a loosely typed command/query whose value depends upon some variable. Each time the command is executed, the result is different, depending on the value of the variable. It cannot be predetermined and usually comes under dynamic programming SQL query. An ad hoc query is short lived and is created at runtime.