Watch Our YouTube Channel
It is currently Mon Jan 22, 2018 10:34 am

All times are UTC

Post new topic Reply to topic  [ 1 post ] 
Author Message
PostPosted: Thu Apr 21, 2016 8:46 am 

Joined: Fri Nov 13, 2015 3:56 am
Posts: 460
Stored Procedures:
1)These are useful when you have a function that changes relatively little or has parameters easily encompassed within the syntax of a stored procedure.
2)Stored procedures are not portable between databases, because there is no industry standard for PL/SQL and even if there was a standard, you usually end up using database-specific functionality/sql inside your stored procedures.
3)Stored procedure languages have limited (if any) libraries to draw upon, so you end up writing lots of low-value code.
4)Allow clean permissioning control over complex read and write queries.
5)Stored Procedures are compiled and runs on the server.
6)Stored procedures are stored in data base in complied form.
7)Execution plan retention and reuse
8)Query auto-parameterization
9)Access to database objects that is both secure and uniform
10)Consistent, safe data modification
11)Application modularization.
12)Good for short, simple queries.
13)Easy to maintain and easy to troubleshoot.
14)Increased data security and integrity.
15)Hard to code flexible logic compared with dynamic SQL.
16)Allows you to isolate your schema from your data access code.
17)Discrete and versionable.

Dynamic SQL:
1)It is generally for quick-and-dirty work. It's also used when dealing with something that needs to be assembled almost entirely from scratch.
2)A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set of statements stored in a variable and executed using a SQL command.
3)It is the process of generating, preparing, and executing SQL statements at run time using commands.
4)Dynamic SQL allows retrieval of a prepared statement’s parameter formats.It is More difficult to troubleshoot.
5)These are not compiled and runs on client.
6)A dynamic SQL statement can be replaced by a stored procedure that returns the same results.
7)It is primarily useful for precompiler support, but it can also be used by interactive applications.
8)In this sql the statements are dynamically constructed at run time.
9)Good for short, simple queries.
10)Makes you tied to that SQL vendor.
11)More vulnerable to SQL injection attacks.
12)Allows any table, database, or column to be referenced.
13)Keeps database logic separate from business logic.

Ad Hoc SQL:
1)Good for long, complex quieres.
2)Flexible data access.
3)More difficult to maintain, if you don't use an ORM.
4)ORM usage is possible; can be compiled/tested in code.
5)More bits transferred over network.
6)More difficult to troubleshoot, if you don't use an ORM.
7)Compiled at runtime, Execution Plan stored in Cache.
8)Changed data is no issues because of re-compile.
9)Only 1 statement possible.
10)Can be changed simultaneously with structure changes.

Refer some extra differences of these sql: ... namic-sql/

Learn Web Development | Latest Technology News |Motivational Quotes

Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: