We never
tried to find out the exact difference between SQL stored procedure and inline
sql statement in our application. We just assume like both functionalities are
same. But it exactly it is not. We overcome the inline sql statement using
stored procedure; using stored procedure is not just make our application suitable
but also make sql query (procedure) reusable. Here we found some important
points which put in the picture to use SQL stored procedure not inline sql
query. Here we go...
a.
SQL procedure does not impact our running application. Because
it is on our sql server not in our application whereas in SQL statement are
used in application side, we need to make changes in our application so some
how we need to take some down time for.
b.
SQL Procedures are reusable (it can be used in another
Forms/ Pages/ Classes) while inline SQL statement is not reusable it can be
only used in the same form or pages.
c.
SQL Procedure takes less development time and convenient.
It is very easy to debug our query while in SQL statement we can not trace
error on immediate effect.
d.
SQL Procedure
can be used to log errors using built-in SQL function RAISERROR to writing the
error log.
e.
SQL Procedure prevents SQL injection which is very
dangerous for our application database while SQL statement can not so it is
better to use SQL Procedure.
So by all above
of the points we reached at final conclusion why to use SQL procedure over SQL
statements. It is beneficial in performance (because stored procedures are
already compiled while sql statement compiles every time when executed),
security, reusability and also flexible.
Comments
It's my pleasure to getting your valuable feedback. I keep writing the useful points which is used in our programming life..
Cheers.