What are prepared statements? How are they different from dynamic sql?

Question

I did not see any duplicate questions but wondering if somebody can provide some good examples and specially best practices around these.

Answer 1

Prepared Statements are precompiled statements that you can run multiple times against on the database, and SQLServer won't parse or generate a different execution plan each time you run it. Usually, you run prepared statement in a client context (using JDBC, ADO.NET, ODBC or any other client access technology).

A prepared statement in Java (using JDBC, of course) will be something like:

PreparedStatement ps = conn.prepareStatmente("insert into t(field1) values (?)");
ps.setString(1, "Hello");
ps.executeUpdate();
ps.setStrgin(2, "World");
ps.executeUpdate();
ps.close();
// two rows will be inserted into table t:
// field1 => "Hello"
// field1 => "world"

Dynamic SQL is the ability to run any SQL sentence stored in a dynamic variable (i.e. a string) inside a stored procedure or function in SQLServer. You can find some examples in the link provided.

Answer 2

It'd be easier to explain with some context...

In Java, PreparedStatements are SQL statements in a Java String and supports placeholders you can populate without worrying about escaping single quotes. .NET also has PreparedStatement syntax:

Prepared execution is commonly used by applications to execute the same parameterized SQL statement repeatedly. Prepared execution is faster than direct execution for statements executed more than three or four times because the statement is compiled only once, while statements executed directly are compiled each time they are executed. Prepared execution can also provide a reduction in network traffic because the driver can send an execution plan identifier and the parameter values, rather than an entire SQL statement, to the data source each time the statement is executed.

A step short of using a Stored Procedure...

MySQL's dynamic SQL syntax is also called PreparedStatements...

Dynamic SQL

Dynamic SQL is basically any SQL declared as a string data type - it can be customized before execution. The need for using dynamic SQL comes when you want to do things like dynamic column and/or table references. For example, no SQL supports a string variable to represent the table in the FROM clause (Table Valued Variables are an exception, where supported).

It's important to know the difference between EXEC and EXEC sp_executesql with regard to dynamic SQL on SQL Server. EXEC sp_executesql was added in SQL Server 2005, and you can read more about dynamic SQL on SQL Server in the excellent article: The Curse and Blessings of Dynamic SQL