Tag: Stored Procedures

SQL focuses on structured data computations, yet it lacks procedural control syntax for handling procedure-oriented computations. The SQL-based Stored procedure (SP) is created to make up for this absence by supporting the much-needed syntax. SP also features a series of merits, including code encapsulation, injection prevention and dynamic syntax. In practical use, however, its demerits are unignorable.

There has been a long-standing debate about the stored procedure’s weaknesses. Now let’s again look at its disadvantages. Non-migratable Being migratable refers to the stored procedure’s ability to move from its native database to another database of different type. Lack of this ability makes stored procedure syntax narrowly applicable and costly un-migratable. Hard to edit and debug A stored procedure is implemented within the database without an invalid IDE environment. This makes the hard to edit and debug problem a long-lasting one. Closed system The stored procedure that runs within the database can only compute data in the database. It

The debate on the advantages and disadvantages of stored procedures has long existed. Here again, we will examine them one by one. Pros Of course, stored procedures have their merits because there is no shortage of applications. Procedural SQL coding Seldom is it considered an advantage because people have been accustomed to taking it for granted. SQL syntax requires that data processing code be written in one single statement even if numerous nested queries are needed, bringing disasters for achieving complex data processing logic. Stored procedures enable SQL to accomplish a computation step by step and overcome not a few

Business logic consists of database read & write, structured data computation, and procedural control. The first two parts are what SQL was designed for and thus easy to handle. But SQL lacks procedural control statements (loop and judgement), it cannot achieve the whole business logic on its own. In order to address this issue, database vendors invented a new language based on SQL and procedural control statements. That is stored procedure. The stored procedure has more complete functionalities to achieve business logics independently The language combines SQL and procedural control statements. Their cooperation gives the stored procedure complete functionalities to