10

Is there any alternative to stored procedures, secure and fast as well as stored procs. i know only Hibernate. Is there any other technologies like that?

dotjoe
  • 26,242
  • 5
  • 63
  • 77
  • 18
    I don't think you quite understand stored procedures. – GEOCHET Oct 06 '08 at 17:29
  • Rich B: I wish you posted that as an answer so I can vote it up :) – Milan Babuškov Oct 06 '08 at 17:31
  • 2
    @Milan: Thanks for the agreement, but it is in the proper place. ;) – GEOCHET Oct 06 '08 at 17:33
  • i'm sorry, where i didn't understood sprocs? it takes much development time to write sproc code, and i thought is there any alternative? –  Oct 06 '08 at 17:35
  • See http://stackoverflow.com/questions/167154/logic-database-or-application2-constraints-check for a discussion that's similar to this. – S.Lott Oct 06 '08 at 17:35
  • Also, see http://stackoverflow.com/questions/119540/business-logic-database-or-application-layer for yet another discussion of stored procedures. – S.Lott Oct 06 '08 at 17:36
  • @Serik: You are transposing NHibernate with sprocs for one, and I don't think you actually understand what sprocs are. It might be beneficial if you ask a broader question inviting a little education on sprocs instead. – GEOCHET Oct 06 '08 at 17:47
  • i think i asked question in incorrect way, and now i have different answeres instead which i expected –  Oct 06 '08 at 17:54
  • I think OP had a point, you can work with a database in procedural way (stored procedures) or in object oriented way (ORM). These are not the same things from the technical perspective but they are used for the same goal to process some database operations on the database. I wrote a lot of procedures in legacy applications but I found that ORM is much easier to use and can help you handle more complicated logic since you can deduplicate code easier and divide the operation into smaller parts. On the otherhand you are moving data back and forth. – Muflix Jun 12 '21 at 06:49

8 Answers8

16

Stored procedures are a place to put code (SQL) which executes on the database, so I understand the question to mean

"is there any other way to package up the code which runs on the database?"

There are several answers:

  • There is nothing else that is quite the same as a stored procedure, but there are alternatives which you might consider.
  • You could write all your SQL as strings inside your client code (java or whatever)
    • This has various problems (loss of encapsulation, tight coupling -> harder maintenance), however, and is not a good idea.
  • You could use an ORM such as NHibernate, which inserts a layer between your client logic and the database. The ORM generates SQL to execute on the database. With an ORM, it is harder to express complex business logic than in a stored procedure (sweeping generalisation!).
  • A kind of halfway house is to define your own data access layer (DAL) in java (or watever you're using) and keep it separate from the main body of client code (separate classes / namespaces / etc.), so that your client makes calls to the DAL, and the DAL interprets these and sends SQL to the database, returning the results from the database back to the client.
AJ.
  • 13,461
  • 19
  • 51
  • 63
7

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

1) If you're using MS SQL Server, it will generate a query plan which should enable the stored procedure to execute faster than simple dynamic sql.

2) It can be easier an more effective to fix a bug in a stored procedure, expecially if your application calls that procedure in several spots.

3) I find it's nice to encapsulate database logic in the database rather than in embedded sql or application config file.

4) Creating stored procedure into the database will allow sql server to do some syntax, and validation checks at design time.

Jeremy
  • 44,950
  • 68
  • 206
  • 332
  • 1
    Dynamic SQL can also get query plans, it's a myth (true at one time in the past), that only stored procs get the query plans. – Lance Roberts Oct 06 '08 at 17:38
  • 2
    Dynamic SQL gets a query plan, but if your dynamic sql changes at all due to parameter values, the query plan will have to be rebuilt, which would be a performance hit. – Jeremy Oct 06 '08 at 18:26
  • 1
    Yes, you're right, just like using parameter values can affect stored procs. I liked stored procs also, but for my usual needs I just wish they were more dynamic! – Lance Roberts Oct 06 '08 at 22:40
  • Databases don't have logic - applications do. Coding without SQL or using a DB neutral approach such as NHibernate Criteria or HQL and keeping that logic in the DAO classes makes sense from a OO coders perspective. This and Lance's observations would justify a down vote. – Simon Gibbs Oct 09 '08 at 10:40
  • 5
    Putting essential business logic in the application makes no sense at all from a database specialist's perspective. Databases tend to be accessed from multiple sources. Essential rules must be at the database level or you are going to have data integrity problems. – HLGEM Sep 01 '09 at 17:25
4

Hibernate is an object/relational persistence service.

Stored procedure is a subroutine inside a relational database system.

Not the same thing.

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

Patrick Desjardins
  • 136,852
  • 88
  • 292
  • 341
3

You can do dynamic SQL as secure and fast as stored procedures can be, it just takes some work. Of course, it takes some work to make stored procedures secure and fast also.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
  • Guess there's some stored procs fanboys out there. – Lance Roberts Oct 06 '08 at 17:36
  • 1
    No kidding. Lance, I think your answer might depend a lot on the DB engine you are talking about. – Flory Oct 06 '08 at 17:40
  • Yes, I agree. I tried to keep any detail out of the answer, so as not to start some kind of flamewar. – Lance Roberts Oct 06 '08 at 17:43
  • Dynamic sql is not as secure as a properly written stored proc. You can limit the direct access to tables by using stored procs (as long as they in turn do not use dynamic sql) which helps you protect your database from internal users. (The ones who most frequently steal data or commit fraud.) – HLGEM Oct 06 '08 at 19:15
  • @HLGEM: I'd have to see an example of this to believe it. After all, I can grant select, insert, update and delete as individual privileges to individual users. Not sure how much more secure things can be. – S.Lott Oct 06 '08 at 20:25
  • @S.Lott: your stored procedure can implement a rule that means a particular user can only update a subset of rows, or can only delete rows that are in status "EXPIRED" or whatever. That's why they are tighter for security than granting DELETE on the table. – WW. Aug 13 '09 at 03:40
2

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

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement

..from Wikipedia

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

pinku
  • 115
  • 2
  • 7
1

I think the OP means that an alternative to writing all his database code directly in his application code is either to call stored procedures or to introduce a layer of separation between his application code and database using an ORM such as Hibernate, but yes they are very different things.

Using stored procedures let you keep your SQL in one place separate from your application code. Using Hibernate allows you to avoid writing SQL completely and provides an object representation of the relational database.

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

Paul Taylor
  • 13,411
  • 42
  • 184
  • 351
1

Hmm, seems to me that the obvious alternative to stored procedures is to write application code. Instead of, say, writing a store procedure to post a debit every time a credit is posted, you could write application code that writes both.

Maybe I'm being too simplistic here or missing the point of the question.

Jay
  • 26,876
  • 10
  • 61
  • 112
1

It'd help a little more if you said why you are looking for alternatives, what about stored procs do you not like?

Some databases (eg. PostgreSQL) also allow you to write stored procedures in different languages. So if you really want to you can write them in Python or Java or the like, instead of SQL.

Colin Coghill
  • 1,549
  • 15
  • 18