Dynamic SQL in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 138 of sequel server
tutorial
in this video we'll discuss what is
dynamic sequel and a simple example of
using dynamic sequel so what is dynamic
sequel in simple terms a dynamic sequel
is a sequel built from strings at
runtime
let's understand this with an example
let's say we want to implement an
employee search form that looks like
this notice on the form we've got four
search fields first name last name
gender and salary using this employee
search form we want to search this
employees table it is the sequel script
to create and populate this employee's
table let test data I'll have to script
available on my blog in case you need it
I've already executed the script so to
search this employee's table using this
employees form we may have a stored
procedure that might look like this
notice on the employee search form we've
got four search fields and the search
employee stored procedure has got four
input parameters corresponding to those
search fields and within the body of the
stew procedure we are doing all the rows
and columns from employees table their
first name column equals whatever value
we have passed to the first name
parameter or first in parameter is null
so when it's first in parameter going to
be now if we don't specify anything for
the first name field then this first
name parameter is going to be not so
either the parameter is now or first
name column equals whatever value the
first name parameter has got so if
either of those conditions are true then
give me that employee row and we have
got other conditions as well for each
search field
now here we only have full for search
fields on the form so this stored
procedure is not that complicated now
here we are not using any dynamic sequel
it's all static sequel in a stored
procedure let's quickly test the stored
procedure to make sure it works is
expected at the moment within our
employees table we've got seven
now let's say we want to use our SP
search employee stored procedure and
find all employees whose first name is
Marc and whose last name is Hastings
we're not going to make use of the other
parameters so in this case gender and
Saturday parameters are now so when we
execute this notice we get an employee
whose first name is Marc and last name
is Hastings let's say we want to find
all female employees so in this case
right here except the gender parameter
we have set all the other parameters to
now gender is set to female so when we
execute this we get all female employees
now this stored procedure in this case
is not very complicated as you only have
full search filters but what if there
are 20 or more such filters this stored
procedure can get complex and to make
things worse what if we want to specify
conditions like and or etc between these
search filters this short procedure can
get extremely large complicated and
difficult to maintain one way to reduce
this complexity is by using dynamic
sequel so what we can do here is
depending on for which search filters
the end user has provided the values on
the search page we can build the
bearclaw's
dynamically at runtime which can reduce
complexity however you might hear
arguments that dynamic sequel is bad
both in terms of security and
performance that is true if the dynamic
sequel is not properly implemented from
a security standpoint it may open those
foreseeable injection attack and from a
performance standpoint the cached query
plans may not be properly be used if
properly implemented we will not have
these problems with dynamic sequel in
our upcoming videos we'll discuss good
and bad dynamic sequel implementations
for now let's implement a search example
using dynamic sequel fire up a new query
editor vendor and in here I have
declared a variable at 0 of type and
we're care remember dynamic sequel is
formed from
so in this string variable let's do our
dynamics eco statement let's assume the
end user has not provided values for any
of these search fields that means we
want to load all the rows and columns
from employee's table which also means
we don't have to have this where clause
at all
so depending on the values that the user
is provided for these search fields we
are going to build our Sifl statement so
here our sequel statement is now just
going to be select star from employees
so in this variable we have our dynamic
sequel statement the obvious question
that comes to mind at this point is how
to execute this dynamic sequel statement
to execute the dynamic sequel statement
we use systems to procedure SP
underscore execute SQL and this distance
tool procedure we pass the dynamic
sequel statement that we want to execute
so let's execute all these three
statements together and notice we get
all the rows and columns from our
employees table now let's assume the end
user has provided values for first name
and last name fields on the search form
that means they have to include the
bearclaw's
with first name and last name so to a
dynamic sequel statement I'm going to
append the where clause where first-name
equals at first name so here they're
using parameters within our query and
last name equals at last name now in
this dynamic sequel statement we have
got parameters so we have to declare
these parameter types and to do that I'm
going to declare another variable and
let's call this at params and in this
variable they are going to declare the
parameters that we have in our dynamic
simple statement so set at params equals
so this at first name parameter
is of type n work at and let's set the
length to 100 similarly last name
parameter is also of type n where care
and its length is also 100 now this
system store procedure has got two
predefined parameters the first
parameter is the dynamic sequel
statement that we want to execute and
the second parameter declares the
parameters that we have in our dynamic
sequel statement so in our dynamic
sequel statement as you can see we have
got two parameters at first name at last
name and using this variable we have
declared the parameters so the second
parameter for the system stored
procedure is this params variable which
contains the parameter declarations that
we have in our dynamic single statement
and then we also have to supply values
for these parameters that we have in our
dynamic sequel statement so the rest of
the parameters that we can have for the
system stored procedure are user defined
parameters so now we are going to pass
values for first name and last name
parameters so at first name equals in a
let's say for example mark is the first
name and let's set last name to Hastings
so now let's go ahead and execute this
notice we have an error message it says
in correct syntax near equals let's
understand why we are getting the data
and to do that let's first comment this
execute statement and let's print our c2
statement that we have here so I'm going
to use the print statement and bassett
this variable X equal so when we execute
this look at the query that we have
select star from employees where there
is no space between employees and where
that's the reason why we are getting
that statement and the reason we don't
have that space is because either here
or here
we don't have a space so let's include a
space there and now when we execute this
notice now we have caught space between
employees and where clause so let's
comment this print SQL uncomment our
execute statement and let's execute this
one more time notice now we get mark
Hastings as expected so here is that
dynamic sequel example that we just
looked at
to execute dynamic sequel we use system
stored procedure SP underscore execute
SQL it takes two predefined parameters
and any number of user-defined
parameters as you can see the first
parameter is mandatory and it contains
the dynamic sequel statement that we
want to execute the second parameter is
optional and this parameter is used to
declare the parameters that we have in
our dynamic sequel statement and then we
can have any number of user-defined
parameters so within our dynamic sequel
statement we've got two parameters at
first name and at last name so we are
using the rest of the parameters here to
pass values for those parameters that we
have in our dynamic sequel statement
notice here we are using parameter names
as well the parameter names are optional
so when we use parameter names we can
have those parameters in any order for
example we can have
of a lastname parameter first and then
our first name parameter so when we
execute this it still works exactly the
same way as before but then these
parameter names are optional so if I
don't use parameter names then it's
going to use the parameter position to
specify which value is for which
parameter so here if I am passing
Hastings first then it's going to treat
this value is for first name parameter
and Mark for last name but that's not
what we want we want mark to be the
value for the first name parameter so we
need to specify that in the first
position and then Hastings in the second
position so when we execute this it
again is going to work exactly the same
way as before this is just the
introduction to dynamic sequel if a few
things are unclear at the moment don't
worry in our upcoming videos we will
discuss implementing a real world search
web page with and without dynamic sequel
performance and security implications of
dynamic sequel and along the way we'll
also discuss good and bad dynamic sequel
implementations different options that
are available for us for executing
dynamic sequel and their implications
and finally using dynamic sequel in
stored procedures and implications once
we discuss all these then you will
understand that flexibility dynamic
sequel provides advantages and
disadvantages of dynamic sequel when and
when not to use dynamic sequel so please
stay tuned thank you for listening and
have a great day