Static VS Dynamic SQL

Is dynamic SQL more performant than static SQL in SQL Server?

Dynamic SQL has the advantage that a query is recompiled every time it is run. This has the advantage that the execution plan can take advantage of the most recent statistics on the table and the values of any parameters.

In addition to being more readable, static SQL has the advantage that it does not need to be recompiled -- saving a step in running the query (well, actually two if you count parsing-->compiling-->executing). This may or may not be a good thing.

You can force static plans to recompile using the with (recompile) option.

Sometimes, you need to use dynamic SQL. As a general rule, though, I would use compiler hints and other efforts to manage performance before depending on dynamic SQL.

Static vs dynamic sql

Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.

The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc).

Another reason is that sometimes using Bind variables as parameters can be counter-productive.

An example is if you have something like a status field, where data is not evenly distributed (but is indexed).

Consider the following 3 statements, when 95% of the data is 'P'rocessed

   SELECT col FROM table 
   WHERE status = 'U'-- unprocessed
   AND company = :company

   SELECT col FROM table 
   WHERE status = 'P' -- processed
   AND company = :company

   SELECT col FROM table
   WHERE status = :status
   AND company = :company

In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).

You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.

Downsides

Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.

Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.

A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.

SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7

The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.

Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.

Overheads

The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.

Search Query Dynamic Vs Static - SQL Server 2012

I prefer approach #1.

Easier to maintain the code in #1.
In most cases #1 will be faster than #2.
Handling quotes inside quotes is a headache. ' OR LastName Like ''%' + @LastName+ '%'''
Dynamic sql is harder to debug. May not be in this case.
When your requirement changes your dynamic code will get messier and
messier.
Query plan won't be cached in #2.
SQL Injection as you indicated.

Execute mixed sql: dynamic with static

This should do the job:

@DynamicPrefix = '0001'; -- this is passed in by caller
@EngineCd = '070123456'; -- this is passed in by caller

DECLARE @EngineKey INT;

DECLARE @SQL NVARCHAR (MAX);

SET @SQL =N'set @EngineKey = (select optionnumber from lookup_'+
    @DynamicPrefix +
    '_option_001 where salescode = '+
    @EngineCd +')';

EXECUTE sp_executesql
    @SQL,
    N'@EngineKey INT OUTPUT, @EngineCd VARCHAR(10)',
    @EngineKey OUTPUT, @EngineCd;

You have to specify your output parameter with OUTPUT keyword, and set your variables and their datatypes as you can see in the code.

If you don't use the OUTPUT keyword, your variable will always return NULL.

There are examples provided in the docs, see sp_executesql.

Is static sql to be preferred over dynamic sql in postgresql stored procedures?

The static SQL should be preferred almost time - dynamic SQL should be used only when it is necessary

from performance reasons (dynamic SQL doesn't reuse execution plans). One shot plan can be better some times (and necessary).
can reduce lot of code

In other cases uses static SQL every time. Benefits:

readability
reuse of execution plans
it is safe against SQL injection by default
static check is available