SQL TIPS: DYNAMIC SQL

How to create queries as a string expression.

TABLE OF CONTENT

· CONCEPT
· SECURITY PROBLEMS
· EXECUTING DYNAMIC QUERY AS A STORED PROCEDURE
· CONCLUSION

Hello everyone, how are you!?

We’ve seen how to create temporary tables to store temporary query data, creating more dynamism in database interactions.

So, since we’re talking about dynamic and temporary queries and objects, I’d like to introduce the concept of Dynamic SQL.

CONCEPT

The concept behind this tool is to execute a query as a block of text but that returns an expected result, even if it doesn’t look like we’re performing a query!

When using this feature in the database, it’s as if we’re telling the database: “execute the block of text as if it were a query.” The database interprets the command, executes it, and returns the result as a normal query.

Before we delve any deeper, take a look at this example:

This was a very simple example of using a dynamic query, and now, let’s understand its structure.

1. The dynamic query works like a variable so we need to declare it.