Is there an alternative to stored procedures?

Question

Is there an alternative to stored procedures that are safe and fast, as well as stored processes. I only know Hibernate. Are there any other technologies?

Answer 1

Stored procedures are the place to put the code (SQL) that runs in the database, so I understand what this question means

"is there any other way to package code that runs in the database?"

There are several answers:

• Nothing else is the same as a stored procedure, but there are alternatives that you might consider.
• You can write all your SQL as strings inside your client code (java or something else)
  • It has various problems (loss of encapsulation, tight connection → more complicated maintenance), however, and this is not a good idea.
• You can use ORM, such as NHibernate, which inserts a layer between your client logic and the database. ORM generates SQL to execute in the database. With ORM, it is more difficult to express complex business logic than in a stored procedure (broad generalizability!).
• In some half of the house, you need to define your own data access level (DAL) in java (or whatever you use) and keep it separate from the main client code (separate classes / namespaces / etc. ), so your client makes calls in the DAL, and the DAL interprets them and sends SQL to the database, returning the results from the database back to the client.

Answer 2

Yes. you can use dynamic sql, but I personally like stored procedures.

1) If you use MS SQL Server, it will generate a query plan that should allow the stored procedure to execute faster than simple dynamic sql.

2). It is best to fix the error in the stored procedure, especially if your application calls this procedure in several places.

3) I am pleased to encapsulate the database logic in the database, and not in the embedded sql file or application configuration file.

4) Creating a stored procedure in the database will allow the SQL server to execute some syntax and check validation during development.

Answer 3

Hibernate is an object / relational save function.

A stored procedure is a subroutine within a relational database system.

Not the same.

If you want an alternative to Hibernate, you can check iBatis for Spring

Answer 4

You can make dynamic SQL as safe and fast as stored procedures, it just takes some work. Of course, performing stored procedures also requires some work.

Answer 5

A stored procedure is a routine available to applications, a relational database. Stored procedures (sometimes called proc, sproc, StoPro, or SP) are actually stored in a dictionary database.

Typical uses of stored procedures include data validation in a database) or access control mechanisms. In addition, procedures are used to consolidate and centralize the logic originally implemented in applications. Large or complex processing, which may require the execution of several SQL queries, the statements are moved to stored procedures and all applications only procedures.

Stored procedures are similar to user-defined functions (UDFs). The main difference is that UDF can be used like any other expression inside SQL, while procedures must be called using the CALL statement

.. from Wikipedia

I think you need to read this article and reconsider your question. Hibernate has nothing to do with stored procedures.

Answer 6

This will help a little more if you say why you are looking for alternatives, what about saved procs you don't like?

Some databases (such as PostgreSQL) also allow you to write stored procedures in different languages. Therefore, if you really want you to be able to write them in Python or Java or the like, instead of SQL.

Answer 7

Hmm, it seems to me that an obvious alternative to stored procedures is to write application code. Instead of, say, writing a vault procedure to post debits each time you send a loan, you can write an application code that writes both.

Maybe I'm too simplified here or don't see the point in the question.

Answer 8

I think OP means that the alternative to writing all the code for your database directly in its application code is either calling stored procedures or introducing a separation level between its application code and the database using ORM, for example, Hibernate, but yes they are completely different .

Using stored procedures allows you to store your SQL in one place separate from your application code. Using Hibernate avoids writing SQL completely and provides an object representation of a relational database.

The way you go depends on the application and your own preferences.