Copyright

What is Dynamic SQL?

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

If only SQL statements could write themselves! Dynamic SQL lets us get close: statements are created when the query runs. This lesson will cover dynamic SQL and provide examples of this powerful concept.

Dynamic SQL

Not all SQL queries are run by a database administrator sitting at their desk. Each time you search Amazon, there isn't a person sitting on the other end of the Internet, keying your request into an SQL query.

Dynamic SQL allows a database administrator to write code that is flexible and adaptable. It basically means that the full SQL statement won't be known until the program runs. Think of the search example above: When a user searches Amazon, we don't know which table we should query, or what to search for until they hit Enter.

It is a powerful tool because it lets us write programs with SQL statements that aren't necessarily known until the program runs. Dynamic SQL refers to the SQL statements that are generated at the time the program runs. As a database administrator, you necessarily know what will be executed.

Let's take the following simple example, where we want to select an artist from a table. But we're not sure which table (maybe it's from an archived table, or the live production table), and we don't know the primary key value.


SELECT artistID, artistName
FROM ????
WHERE artistID = ??


In this case, we need a user (or another program) to provide the values. But they won't be provided until our query runs, so we can't build it with the values.

Dynamic SQL In Action

Let's take a look at an example of dynamic SQL in action. The code is written for SQL Server, although other database management systems are equally viable.

Below is a simple example using the scenario from above. Notice that the 'at' (@) symbol goes in front of the fields we've defined as dynamic. For example, if you have the user enter the band name, the parameter could be named @bandName. It's also in front of the variable cmd. This is because we're building the entire SELECT statement as a string.


DECLARE @tableName varchar(100)
DECLARE @artistID int
DECARE @sql varchar(4000)
SET @cmd = "SELECT artistID, artistName FROM + " + @tableName + "WHERE artistID = " + @artistID
  EXEC(@cmd)


Although dynamic SQL can be very powerful for creating flexible SQL queries, they can also have a negative impact on system performance. The more the system or server has to process, the slower it works. If the query has dozens of parameters, each one has to be evaluated and it takes time. It's not as easy on the system as a straightforward query with defined values.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support