YT YouTube2Text

YouTube2Text → Transcripts → Dynamic SQL in SQL Server

Dynamic SQL in SQL Server — Transcript

by kudvenkat · 1,772 words · 267 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00This is part 138 of SQL Server tutorial.
0:03In this video, we'll discuss what is
0:05dynamic SQL and a simple example of
0:08using dynamic SQL. So, what is dynamic
0:11SQL? In simple terms, a dynamic SQL is a
0:15SQL built from strings at runtime. Let's
0:18understand this with an example. Let's
0:20say we want to implement an employee
0:22search form that looks like this. Notice
0:25on the form, we've got four search
0:27fields. first name, last name, gender,
0:30and salary. Using this employee search
0:33form, we want to search this employees
0:36table. Here is the SQL script to create
0:38and populate this employees table with
0:41test data. I'll have the script
0:43available on my blog in case you need
0:45it. I've already executed the script. So
0:48to search this employees table using
0:51this employees form, we may have a store
0:54procedure that might look like this.
0:57Notice on the employee search form we
0:59have got four search fields and the
1:01search employee store procedure has got
1:04four input parameters corresponding to
1:07those search
1:08fails and within the body of the store
1:10procedure we are retrieving all the rows
1:12and columns from employees table where
1:16first name column equals whatever value
1:19we have passed to the first name
1:20parameter or first name parameter is
1:23null. So when is first name parameter
1:25going to be null? If we don't specify
1:27anything for the first name field, then
1:30this first name parameter is going to be
1:32null. So either the parameter is null or
1:35first name column equals whatever value
1:38the first name parameter has got. So if
1:41either of those conditions are true,
1:43then give me that employee row. And we
1:46have got other conditions as well for
1:48each search field. Now here we only have
1:52f four search fields on the form. So the
1:55store procedure is not that complicated.
1:57Now here we are not using any dynamic
1:59SQL. It's all static SQL in a stored
2:02procedure. Now let's quickly test this
2:05store procedure to make sure it works as
2:08expected. At the moment within our
2:10employees table we have got seven rows.
2:13Now let's say we want to use our SP
2:15search employee store procedure and find
2:18all employees whose first name is Mark
2:20and whose last name is Hastings. We're
2:23not going to make use of the other
2:25parameters. So in this case gender and
2:28salary parameters are null. So when we
2:30execute this notice we get an employee
2:34whose first name is Mark and last name
2:36is Hastings. Now let's say we want to
2:39find all female employees. So in this
2:41case right here except the gender
2:43parameter we have set all the other
2:45parameters to null. Gender is set to
2:47female. So when we execute this we get
2:50all female
2:52employees. Now this procedure in this
2:55case is not very complicated as we only
2:58have four search filters. But what if
3:01there are 20 or more such filters? This
3:04store procedure can get complex. And to
3:06make things worse, what if we want to
3:08specify conditions like and, or, etc.
3:12between these search filters? The stored
3:14procedure can get extremely large,
3:17complicated, and difficult to maintain.
3:19One way to reduce this complexity is by
3:22using dynamic SQL. So what we can do
3:24here is depending on for which search
3:27filters the end user has provided the
3:30values on the search page we can build
3:32the wear clause dynamically at runtime
3:35which can reduce complexity. However you
3:38might hear arguments that dynamic SQL is
3:41bad both in terms of security and
3:43performance. That is true if the dynamic
3:47SQL is not properly implemented. From a
3:49security standpoint, it may open doors
3:52for SQL injection attack. And from a
3:54performance standpoint, the cache query
3:56plans may not be properly reused. If
3:59properly implemented, we will not have
4:01these problems with dynamic SQL. In our
4:04upcoming videos, we'll discuss good and
4:07bad dynamic SQL implementations. For
4:10now, let's implement the search example
4:12using dynamic SQL. Fire up a new query
4:16editor vendor. And in here I've declared
4:19a variable at SQL of type envir.
4:22Remember dynamic SQL is formed from
4:25strings. So in this string variable,
4:29let's store our dynamic SQL statement.
4:31Let's assume the end user has not
4:34provided values for any of these search
4:36fields. That means we want to load all
4:39the rows and columns from employees
4:41table. Which also means we don't have to
4:44have this wear clause at all. So
4:47depending on the values that the user
4:48has provided for these search fields,
4:51we're going to build our SQL statement.
4:54So here our SQL statement is now just
4:56going to be select star from
5:00employees. So in this variable we have
5:02our dynamic SQL statement. The obvious
5:05question that comes to our mind at this
5:06point is how to execute this dynamic SQL
5:09statement. To execute the dynamic SQL
5:12statement, we use system store procedure
5:17sp_execute SQL. And to this system store
5:20procedure, we pass the dynamic SQL
5:23statement that we want to execute. So
5:26let's execute all these three statements
5:28together. And notice we get all the rows
5:31and columns from our employees table.
5:34Now let's assume the end user has
5:37provided values for first name and last
5:39name fields on the search form. That
5:42means we have to include the wear clause
5:44with first name and last name. So to our
5:47dynamic SQL statement I'm going to
5:49append the wear clause where first name
5:55equals at first name. So here we're
5:59using parameters within our query and
6:02last name equals at last
6:07name. Now in this dynamic SQL statement
6:10we have got parameters. So we have to
6:12declare these parameter types. And to do
6:15that I'm going to declare another
6:18variable. And let's call this at params.
6:23And in this
6:25variable we're going to
6:27declare the parameters that we have in
6:30our dynamic SQL statement. So set at
6:34params
6:35equals. So this at first name
6:39parameter is of type nare care and let's
6:43set the length to 100.
6:48Similarly, last name parameter is also
6:52of type nvac care and its length is also
6:55100. Now the system store procedure has
6:59got two predefined parameters. The first
7:02parameter is the dynamic SQL statement
7:04that we want to execute and the second
7:06parameter declares the parameters that
7:09we have in our dynamic SQL statement. So
7:12in our dynamic SQL statement as you can
7:14see we have got two parameters at first
7:16name at last name and using this
7:18variable we have declared the
7:20parameters. So the second parameter for
7:24the system store procedure is this
7:26params variable which contains the
7:29parameter declarations that we have in
7:33our dynamic SQL statement. And then we
7:36also have to supply values for these
7:38parameters that we have in our dynamic
7:41SQL statement. So the rest of the
7:43parameters that we can have for the
7:45system store procedure are user defined
7:48parameters. So now we are going to pass
7:50values for first name and last name
7:53parameters. So at first
7:57name
7:59equals you know let's say for example
8:02Mark is the first name
8:07and let's set last
8:10name to
8:18Hastings. So now let's go ahead and
8:21execute this.
8:23Notice we have an error message. It says
8:25incorrect syntax near equals. Let's
8:28understand why we are getting that
8:30error. And to do that, let's first
8:32comment this execute statement and let's
8:35print our SQL statement that we have
8:37here. So I'm going to use the print
8:39statement and pass it this variable at
8:42SQL. So when we execute this, look at
8:46the query that we have. select star from
8:49employees where there is no space
8:52between employees and where that's the
8:54reason why we are getting that statement
8:56and the reason we don't have that space
8:58is because either here or here we don't
9:02have a space so let's include a space
9:04there and now when we execute
9:07this notice now we have got space
9:09between employees and the wear clause so
9:13let's comment this print SQL uncomment
9:16our execute statement
9:19And let's execute this one more time.
9:22Notice now we get Mark Hastings as
9:26expected. So here is the dynamic SQL
9:29example that we just looked at. To
9:31execute dynamic SQL, we use system store
9:34procedure
9:35SP_execute SQL. It takes two predefined
9:38parameters and any number of userdefined
9:41parameters. As you can see the first
9:43parameter is mandatory and it contains
9:46the dynamic SQL statement that we want
9:48to execute. The second parameter is
9:50optional and this parameter is used to
9:53declare the parameters that we have in
9:56our dynamic SQL statement. And then we
9:59can have any number of userdefined
10:01parameters. So within our dynamic SQL
10:03statement we have got two parameters at
10:06first name and at last name. So we using
10:09the rest of the parameters here to pass
10:12values for those parameters that we have
10:15in our dynamic SQL statement. Notice
10:17here we are using parameter names as
10:19well. The parameter names are optional.
10:22So when we use parameter names, we can
10:25have those parameters in any order. For
10:27example, we can have our last name
10:31parameter first and then our first name
10:34parameter. So when we execute this, it
10:37still works exactly the same way as
10:39before. But then these parameter names
10:42are optional. So if I don't use
10:44parameter names, then it's going to use
10:47the parameter position to specify which
10:50value is for which parameter. So
10:53here if I am passing Hastings first,
10:56then it's going to treat this value is
10:59for first name parameter and mark for
11:02last name. But that's not what we want.
11:04We want mark to be the value for the
11:07first name parameter. So we need to
11:08specify that in the first position and
11:11then Hastings in the second position. So
11:13when we execute this, it again is going
11:16to work exactly the same way as
11:18before. This is just the introduction to
11:21dynamic SQL. If a few things are unclear
11:24at the moment, don't worry. In our
11:26upcoming videos, we will discuss
11:28implementing a real world search web
11:30page with and without dynamic SQL,
11:33performance and security implications of
11:35dynamic SQL, and along the way we'll
11:38also discuss good and bad dynamic SQL
11:41implementations, different options that
11:43are available for us for executing
11:45dynamic SQL and their implications and
11:48finally using dynamic SQL in store
11:51procedures and implications. Once we
11:54discuss all these then you will
11:56understand the flexibility dynamic SQL
11:59provides, advantages and disadvantages
12:01of dynamic SQL, when and when not to use
12:04dynamic SQL. So please stay tuned. Thank
12:08you for listening and have a great day.

About this transcript

This page contains the full transcript of Dynamic SQL in SQL Server by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 1,772 words across 267 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.