Dynamic SQL vs Stored Procedure Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 147 of sequence of a
tutorial in this video we'll discuss the
advantages and disadvantages of dynamic
sequel and stored procedures let's
understand the advantages and
disadvantages from these aspects first
let's look at separating database logic
from business logic stored procedures
allow us to keep database logic separate
from the business logic the benefit of
keeping database logic separate from the
business logic is that if there is an
issue with the business logic you know
you only have to check the application
code on the other hand if the issue is
with the database logic you only have to
check and modify the stored procedure
another added benefit here is that if
you change the stored procedure there is
no need to compile your application code
and deploy it just modify the stored
procedure and you're done you will lose
this benefit if you are composing your
dynamic sequel statements in client code
as you'll have to change the application
code if there is a bug changing the
application code requires compliation
build and deployment next let's look at
the advantages and disadvantages from
network traffic standpoint stored
procedures reduce network traffic as
only the procedure name and a few
parameters need to be sent over the
network with dynamic sequel you'll have
to send your entire sequel statement
over the network for example if the
dynamic sequel query is a complex one
let's 50 to 60 lines imagine the
increased network traffic between the
client application and database server
next signal injection attacks stored
procedures prevent sequel injection
attacks in general dynamic sequel open
doors for sigil injection attacks if not
careful however even the dynamic sequel
we can prevent sequel injection attacks
by using parametrized queries in some
cases where you need to pass a table
name or a column name as a parameter
it's not possible to use parametrized
queries with dynamic sequel
even in such cases we can use codename
function to prevent sequel injection
attacks
cashed query plan reuse stored
procedures provide increased performance
as cashed query plans reusability
increases even the dynamics equal if we
use parametrized queries cached query
plan tree usability increases which in
turn increases the performance if you're
not using parametrized queries secrets
of an auto parameterization feature can
automatically detect parameter values
and create parameterised queries which
promotes query planned reusability and
in turn performance but one important
thing to keep in mind is that from a
performance standpoint OLTP queries
benefit from cached query plan reuse
however with OLAP systems as your data
drifts and optimize a choices change
OLAP queries benefit from unique plans
so query plan tree use may not be
desirable in this case for performance
maintenance with static sequel in a
stored procedure a syntax error is
reported immediately so ease of writing
is definitely one of the benefits of
using it's true procedure on the other
hand if you have been Amex equal
industry procedure and if there is a
syntax error you wouldn't know it until
you run it stored procedures with static
sequel but also easy to maintain as you
can use SP depends procedure to check
the dependencies on other sequel objects
for example let's say you have a
database with a lot of tables and you
want to know if a certain table is
referenced because you're considering
changing or dropping that specific table
in this case using SP depends but let us
know if that specific table is
referenced anywhere so we can make
changes without breaking anything on the
other hand if you're using dynamic
sequel in a stew procedure or sending it
from a client you will lose this benefit
let's look at a simple example of this
now here we've got two procedures my
procedure 1 and my procedure two within
my procedure one we've got static sequel
and within my procedure two we've got
dynamic sequel notice both the
procedures are dependent on employees
table and here is what we want to do we
want to drop
employees table but before that we want
to check if there are any other objects
dependent on this employees table so we
can make our changes without breaking
anything now want me to find
dependencies is by using the system's -
a procedure SP depends so here we are
checking if there are any dependencies
on the employees table and when we
execute this notice it only reports that
my procedure 1 is dependent on employees
table but in our case we know my
procedure 2 is also dependent on
employees table but sequel server is not
able to report this dependency because
we are using dynamic sequel finally
let's discuss the advantages and
disadvantages from implementing flexible
logic standpoint
sometimes with stored procedures it is
hard to implement flexible logic
compared with dynamic sequel for example
let's say we want to implement they
searched stood procedure with 20 or more
filters the store procedure can get
complex to make things worse what if we
want to specify conditions like and or
etc between these search filters the
procedure can get extremely large
complicated and difficult to maintain
want me to reduce this complexity is by
using dynamic sequel depending on for
which search filters the user has
provided the values on the search page
we build the where Clause dynamically at
runtime which can significantly reduce
the complexity thank you for listening
and have a great day