DIfference Between Stored Procedures and Prepared Statements?

stored-procedures prepared-statement
arsenal picture arsenal · Sep 4, 2011 · Viewed 29.3k times · Source

What is the difference between Stored Procedures and Prepared Statements... And which one is better and why...!! I was trying to google it but haven't got any better article...

Answer

Throoze picture Throoze · Sep 4, 2011

Stored procedures are a sequence of instructions in PL/SQL language. Is a programming language implemented by some DBMS, that lets you store sequences of queries frequently applied to your model, and share the processing load with the application layer.

Prepared statements are queries written with placeholders instead of actual values. You write the query and it is compiled just once by the DBMS, and then you just pass values to place into the placeholders. The advantage of using prepared statements is that you enhance the performance considerably, and protect your applications from SQL Injection.

The difference is you cant store prepared statements. You must "prepare" them every time you need to execute one. Stored procedures, on the other hand, can be stored, associated to a schema, but you need to know PL/SQL to write them.

You must check if your DBMS supports them.

Both are very usefull tools, you might want to combine.

Hope this short explanation to be useful to you!

Related questions

Prepared Statement vs. Stored Procedure

If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc)

Read More
stored procedures as queries: CallableStatement vs. PreparedStatement

PostgreSQL documentation recommends using a CallableStatement to call stored procedures. In the case of a stored procedure that returns a rowset, what are the differences between using CallableStatement: String callString = "{ call rankFoos(?, ?) }"; CallableStatement callableStatement = con.prepareCall(callString); callableStatement.setString(1, fooCategory); …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More