Dynamic SQL: A Guide for SQL Server, Oracle, MySQL, and PostgreSQL

Dynamic SQL is a handy feature of a database that lets you construct SQL statements in a different way.

In this guide, you’ll learn:

• what dynamic SQL is
• see several examples in SQL Server, Oracle, MySQL, and PostgreSQL
• learn about the advantages and disadvantages

Let’s take a look at it.

 

What is Dynamic SQL?

Dynamic SQL is a technique in SQL that lets you construct an SQL statement dynamically as the code is being run. You can create a general SQL statement, where you don’t know the full statement when you write it, but is completed when the code is run.

One example of using this is where you don’t know what table you want to query until the code is run. You can use dynamic SQL to create a query that is run against a table you specify from code as you run the code.

 

Static SQL

The “regular” type of SQL, or the alternative to dynamic SQL is static SQL, which is the standard SQL or SQL that you create and execute at the same time.

If we wanted to select some data from a customer table using static SQL, our code could look like this:

SELECT id, customer_name
FROM customer
WHERE status = 1;

This statement is simply run on the database and the results are returned.

 

Dynamic SQL

Dynamic SQL is created by placing the SQL statement inside a string variable and then running that as a statement.

The syntax is different for each database, but it generally looks like this:

DECLARE statement VARCHAR(2000);
SET statement = 'SELECT id, customer_name
FROM customer
WHERE status = 1';
EXECUTE statement;

The variable of “statement” is declared, set to a SELECT query, and is then executed.

The benefit of dynamic SQL is that you can construct the statement however you like, as it’s just a string value. You can use concatenation to add in different values, columns, or tables, for example.

 

Advantages and Disadvantages of Dynamic SQL

There are some advantages and disadvantages of dynamic SQL.

Advantages:

• More flexibility so you can create statements based on other code you run.
• You can often run code that isn’t available with static SQL.

Disadvantages:

• Higher risk of SQL injection, a technique used to access areas of the database that are unintended.
• Harder to validate the SQL as it’s created as the code is run, meaning a higher chance of errors

Generally, you should try to use static SQL instead of dynamic SQL where you can, and only use dynamic SQL where you can’t use static SQL.

Let’s take a look at some examples of dynamic SQL to better understand it.

 

Dynamic SQL in SQL Server

Dynamic SQL in SQL Server can be created by declaring a variable, specifying what that variable is, and running the statement.

Let’s take a look at some examples.

 

Simple Example

Here’s a simple example of dynamic SQL in SQL Server.

DECLARE @customerStatus INT = 1;
DECLARE @statement VARCHAR(4000);
BEGIN
  SET @statement = 'SELECT id, customer_name
  FROM customer
  WHERE status = @customerStatus';
  EXECUTE sp_executesql @statement;
END;

If you run this code, you’ll get the output of the SELECT statement in your output window.

Let’s look into this code a bit more.

• The first line declares a variable called customerStatus and assigns it the value of 1.
• The second line declares a variable called statement which is a type of varchar and a length of 4000.
• The Begin statement starts the execution of the code.
• The Set statement will set the variable of statement to the value that comes after the = sign. In this situation, it’s the SELECT statement.
• The Set statement includes a mention of the variable @customerStatus, which is substituted for its value, which is 1.
• The Execute statement is run, which calls a stored procedure in SQL Server called sp_executesql. This will execute the code in the statement variable.

The statement variable is set to: SELECT id, customer_name FROM customer WHERE status = 1.

This statement is then run on the database and the results are shown.

 

Determine the Table at Runtime

Another thing you can do with dynamic SQL is determine the table to query at runtime, or after you write the code.

Let’s say you had a stored procedure that queried customer data, but the customer data was stored in two tables: one for active customers and one for inactive customers. The stored procedure would accept a parameter of active or inactive, and return the results.

One way to do it would be to have an IF statement for this parameter.

CREATE PROCEDURE findCustomers
@activeCustomers INT
AS
IF (@activeCustomers = 1)
  BEGIN
    SELECT id, customer_name
    FROM customer;
  END;
ELSE
  BEGIN
    SELECT id, customer_name
    FROM customer_archive;
  END;

This code would work, but you can see there is some duplicated code here. Both SELECT statements are very similar.

An alternative to this would be to determine the table type and then create the query for that table type, which can be done using dynamic SQL.

CREATE PROCEDURE findCustomers
@activeCustomers INT
AS
DECLARE @customerTable VARCHAR(100);
DECLARE @statement VARCHAR(4000);
IF (@activeCustomers = 1) SET @customerTable = 'customer';
ELSE SET @customerTable = 'archive_customer';
SET @statement = 'SELECT id, customer_name
FROM @customerTable';
EXECUTE sp_executesql @statement;

This code will determine the table to use based on the activeCustomers parameter. It then constructs the SQL query using that table, and runs the query.

 

Using Dynamic SQL with Parameters

You can create and run dynamic SQL with parameters using the sp_executesql command.

The command would look like this:

EXECUTE sp_executesql @statement, @parameterDefinition, @param1=value1...

This is made up of several parts:

• @statement: the statement we want to execute.
• @parameterDefinition: a string that has all of the parameters we reference in the statement, including name and data type, separated by a comma.
• @param1 is the first parameter defined in the @parameterDefinition, and then you specify its value.

Multiple parameters can be added to the end of this statement.

A simple example of this concept can be demonstrated with this code, which multiplies the two numbers provided to it.

DECLARE @statement VARCHAR(4000);
SET statement = 'SELECT @num1 * @num2';
EXECUTE sp_executesql @statement, '@num1 int, @num2 int', @num1=3, @num2=4;

The result from this code will be the number 12 shown in the output. The two inputs, num1 and num2, are specified at the time we execute the statement. We can use different values for these parameters just by changing the values when we run the execute command.

 

Alternative to sp_executesql

The examples above use the inbuilt procedure called sp_executesql to run the dynamic SQL.

There is another way to do this in SQL Server.

You can use the EXEC or EXECUTE command without sp_executesql, but you just need to include the statement inside brackets:

DECLARE @statement VARCHAR(4000);
SET @statement = 'SELECT 1';
EXECUTE (@statement):

This will show the value of 1. You would likely use a more complex query here than just SELECT 1, but this just demonstrates the concept.

 

SP_Executesql vs EXECUTE

So there are two ways to run dynamic SQL in SQL Server. Which one should you use?

Microsoft, the vendor of SQL Server, recommends using sp_executesql. There are several reasons for this.

• It’s easier to use the same execution plan multiple times, meaning better performance when the code is run more than once.
• The code is more readable with parameterised queries using sp_executesql.
• It’s less vulnerable to SQL injection attacks, which is where an SQL statement can be changed to run malicious SQL code on the database.

So that’s how you can use dynamic SQL in SQL Server.

 

Dynamic SQL in Oracle

Oracle database also includes the ability to run dynamic SQL. It works by declaring a variable to hold the SQL statement and running the value of that variable.

Let’s see some examples.

 

Simple Example

Here’s a simple example of constructing dynamic SQL in Oracle using a PL/SQL code block.

DECLARE
  customer_status INTEGER := 1;
  statement VARCHAR2(4000);
BEGIN
  statement := 'SELECT id, customer_name
  FROM customer
  WHERE status = :customer_status';
  EXECUTE IMMEDIATE statement;
END;

This code will run the SELECT statement inside the statement variable. Let’s look into this code.

• The PL/SQL code will start with the DECLARE statement.
• Inside the DECLARE section, we declare two variables. The first is called customer_status and is set to 1. The second is called statement, and is a VARCHAR2, which will be the statement we run.
• We then begin the code execution portion with the BEGIN statement.
• The statement variable is set to a SELECT statement, which contains the variable of customer_status. This includes a : before the variable name, which means the value is substituted into the code.
• The EXECUTE IMMEDIATE statement is then run, which runs the SQL inside the statement variable.

So that’s a simple example of dynamic SQL in Oracle.

 

Dynamic SQL with Output

You can use the Execute Immediate feature to save the results of your SQL query into a variable. This can be used in PL/SQL to output the value to the screen or the output panel.

Here’s the same SQL as above, expanded to save the results into a variable.

DECLARE
  customer_status INTEGER := 1;
  statement VARCHAR2(4000);
  customer_id INTEGER;
  customer_name VARCHAR2(200);
BEGIN
  statement := 'SELECT id, customer_name
  FROM customer
  WHERE status = :customer_status';

  EXECUTE IMMEDIATE statement
  INTO customer_id, customer_name;
  DBMS_OUTPUT.PUT_LINE('ID: ' || customer_id || ', Name: ' || customer_name);
END;

This code includes two more variables, customer_id and customer_name. The Execute Immediate command has the extra parameter of INTO, which will store the results from the query into these two variables.

The DBMS_OUTPUT.PUT_LINE function will display the results on a single line of output.

If there is more than one result, you’ll need to loop through the results here to display them, but this code just demonstrates the concept of dynamic SQL.

 

DBMS_SQL Package

An alternative way to run dynamic SQL in Oracle exists, and that’s the DBMS_SQL package.

This package includes several procedures for processing and running dynamic SQL:

• Open a cursor
• Parse a cursor
• Supply bind variables
• Define columns
• Execute the query
• Fetch values
• Close the cursor

Here’s an example of the earlier code but using the DBMS_SQL package.

DECLARE
  customer_status INT := 1;
  statement VARCHAR2(4000);
  customer_id INTEGER;
  customer_name VARCHAR2(200);
  cursor_handle INTEGER;
  rows_processed INTEGER;
BEGIN
  statement := 'SELECT id, customer_name
  FROM customer
  WHERE status = :customer_status';
  
  --Open Cursor
  cursor_handle := DBMS_SQL.OPEN_CURSOR;

  --Parse statement
  DBMS_SQL.PARSE(cursor_handle, statement, DBMS_SQL.native);

  --Supply inputs
  DBMS_SQL.BIND_VARIABLE(cursor_handle, ':customer_status', customer_status);

  --Execute code
  rows_processed := DBMS_SQL.EXECUTE(cursor_handle);

  --Close cursor
  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
END;

This code does the same thing as earlier code examples, except there are more steps involved. The inbuilt DBMS_SQL package is used which can validate inputs at each step, and this can be helpful.

 

DBMS_SQL vs Native Dynamic SQL

Here are the differences between using the DBMS_SQL package, and using the native dynamic SQL (which is constructing the SQL statement using variables and string concatenation).

DBMS_SQL	Native Dynamic SQL
Code is longer and possibly harder to understand	Code is shorter and easier to understand
Generally slower than Native Dynamic SQL	PL/SQL has built-in support so is more efficient
Supports single and multiple row deletes and updates with a returning clause	Supports single row deletes and updates with a returning clause, but not multiple row
Parsed once and executed many times	Parsed every time it is run
Does not support user-defined types	Supports user-defined types
Does not support fetching into records	Supports fetching into records
Supported in client-side programs	Not supported in client-side programs
Supports DESCRIBE	Does not support DESCRIBE
Supports statements larger than 32kb	Does not support statements larger than 32kb

So that’s how you can work with dynamic SQL in Oracle.

 

Dynamic SQL in MySQL

MySQL supports dynamic SQL in recent versions. However, they are called “prepared statements”, so if you’re looking for dynamic SQL in MySQL you’ll likely find more information on prepared statements.

Let’s see an example of this in MySQL.

 

Simple Example

Here’s a simple example of a prepared statement in MySQL.

PREPARE statement FROM 'SELECT id, customer_name FROM customer WHERE status =?';
SET @customer_status = 1;
EXECUTE statement USING @customer_status;

This example has only three lines of code:

• The Prepare line will declare a variable called statement, and set it to the SQL query that has been specified. A ? denotes where a parameter can be provided.
• The Set line will set a new variable called customer_status to the value of 1.
• The Execute line will execute the statement and will accept the value of the customer_status variable.

The output will be the result of the SELECT query.

 

Determine Table to Query

You can also create a prepared statement that will determine what table to run on at the time you run it.

Here’s an example. This example has the Set statement before the Prepare statement, which is OK.

SET @table = 'customer';
SET @sqlQuery = CONCAT('SELECT id, customer_name FROM ', @table);
PREPARE statement FROM @sqlQuery
EXECUTE statement;

This will run a query on the customer table and show the results.

 

Dynamic SQL in PostgreSQL

PostgreSQL supports dynamic SQL just like Oracle, SQL Server, and MySQL.

I found the official documentation confusing, and several StackOverflow answers didn’t really explain how to write one from start to finish. Several blog posts were also complicated and some were even incorrect.

 

Simple Example

Here’s a simple example of running dynamic SQL in PostgreSQL. We’ve created a function to demonstrate the concept.

CREATE OR REPLACE FUNCTION getCustomer(customer_status INTEGER)
RETURNS SETOF customer_test AS
  $$
DECLARE
  sqlQuery VARCHAR(2000);
BEGIN
  sqlQuery = 'SELECT id, customer_name, status FROM customer_test WHERE status = $1';
  RETURN QUERY EXECUTE sqlQuery USING customer_status;
END
$$ LANGUAGE plpgsql;

This code can be called with the following statement:

SELECT * FROM getCustomer(1);

Here’s how the code works:

• The first line creates a new function or replaces an existing function.
• The customer_status is a parameter to the function.
• The Returns line will return an entire record of customer_test, which is what the SETOF keyword does.
• The $$ indicates the delimiter, which means that any semicolons inside after the $$ and before the final $$ are not treated as the end of the function.
• We declare a variable called sqlQuery to hold the statement.
• This variable is set in the Begin block.
• The sqlQuery includes a $1, which indicates that it is a parameter and will be provided at runtime.
• The Return Query Execute will run the specified command and return the results.
• The Using keyword allows you to specify parameters for the query. In this case, the customer_status value which is provided to the function is substituted where the $1 is in the sqlQuery value.
• We then end the function with END and $$.
• The last line defines the language as plpgsql which is how you define functions in PostgreSQL.

When you run the SELECT statement, you select from the function, and the function parameter is the value of 1 which is the customer_status value.

 

Conclusion

Dynamic SQL is possible in SQL Server, Oracle, MySQL, and PostgreSQL. It has the advantage of being flexible and is good for situations where you don’t know the entire query when you write the code (e.g. selecting from different tables). However, it does have its drawbacks, and I would suggest only using dynamic SQL when you can’t get what you need using static or “regular” SQL.