What is the difference between Ad hoc queries and stored procedures?

- Adhoc queries are placed embedded in the business logic code
- Stored procedures are placed embedded in the business logic code
- There is no difference as both are fired on the databases
- None of the above

CORRECT ANSWER : Adhoc queries are placed embedded in the business logic code

Discussion Board
None of the Above is Correct

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.

As the word "ad hoc" suggests, this type of query is designed for a "particular purpose,"which is in contrast to a predefined query, which has the same output value on every execution. An ad hoc query does not reside in the system for a long time and is created dynamically on demand by the user. It is more efficient to use an ad hoc query in programming as it saves system resources, but, at the same time complex, ad hoc queries (have multiple variables) also challenge the processing speed and runtime memory of the system.

sean Fullerton 05-8-2017 02:38 PM

Difference between Ad hoc SQL and Store Procedure:

Stored procedures are simply a group of statements that perform some functions on the database. These functions can be insertion, updating, selecting or deleting rows on one or more database tables.

Ad hoc queries on the other hand serves the same purpose as stored procedures with one big difference. They are assigned to a string and placed embedded in the business logic code.

It is always better to use Stored procedures instead of Ad hoc Queries, since stored procedures can be cached and hence they are much faster. Stored procedures are also safe from the SQL Injections.

Prajakta Pandit 03-10-2017 12:29 AM

Write your comments


Enter the code shown above:
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)