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

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

• There is no difference
• Ad hoc queries are written on the fly
• Stored procedures are embedded on the fly
• Stored procedures are written by Microsoft 

EXPLANATION

Stored Procedures

• Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records)
• Pro: Keeps database logic separate from business logic
• Pro: Easy to troubleshoot
• Pro: Easy to maintain
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Compiled in database
• Pro: Better security (users don't need direct table access)
• Pro: Excellent query plan caching (good for OLTP queries--benefits from plan reuse)
• Con: Excellent query plan caching (bad for OLAP queries--benefits from unique plans)
• Con: Makes you tied to that SQL vendor

Ad Hoc SQL (i.e. created in your business code)

• Pro: Good for long, complex quieres (aka OLAP--i.e. reporting or analysis)
• Pro: Flexible data access
• Pro: ORM usage is possible; can be compiled/tested in code (i.e. Linq-to-Sql or SqlAlchemy)
• Pro: Poor query plan caching (good for OLAP queries--benefits from unique plans)
• Con: Poor query plan caching (bad for OLTP queries--benefits from plan reuse)
• Con: More bits transferred over network (i.e. the whole query and params)
• Con: More difficult to maintain, if you don't use an ORM
• Con: More difficult to troubleshoot, if you don't use an ORM
• Con: More vulnerable to SQL injection attacks

They mentioned that ad-hoc queries are written "on the fly". Does this mean that for a query to be ad-hoc it has to be written at the moment, like in a CLI or a DBMS? What about a query that I just wrote in a script for a specific purpose, is it still considered to be ad-hoc even though it is part of a script?

A "production" system has a lot of "canned queries". Such queries rarely change -- usually only when you "release" a new "version" of the "product".
An "Ad Hoc query" is a SELECT that you make up to look for something for which the canned queries don't help. It often comes from management asking something like "How many foobars happened last week?"
You will run that query once (or until you get the desired output), then toss it. Or you might develop it into a "canned query" for the "production" system, at which point it should no longer be called "ad hoc"