What is a stored procedure? How do they work? What is the make-up of a stored procedure (things each must have to be a Stored Procedure)?
+1
A:
Most of your answers can be found in the Wikipedia entry for stored procedures:
http://en.wikipedia.org/wiki/Stored_procedure
I would also look at the documentation for stored procedures specific to the database that you wish to develop them for.
casperOne
2009-01-19 22:35:55
I'll vote up just because it is an 'answer'. However, I've been quite enamored with longer answers (much like what is included in this post: http://stackoverflow.com/questions/392397/arrays-whats-the-point#392426), and would probably mark such an answer as accepted.
George Stocker
2009-01-19 22:38:03
+1
A:
Generally, a stored procedure is a "SQL Function." They have:
-- a Name
CREATE PROCEDURE spGetPerson
-- Parameters
CREATE PROCEUDRE spGetPerson(@PersonID int)
-- A body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ...
FROM People
WHERE PersonID = @PersonID
This is a T-SQL focused example. Stored procedures can execute most SQL statements, return scalar and table-based values, and are considered to be more secure because they prevent SQL injection attacks.
Dave Swersky
2009-01-19 22:45:29
+1
A:
Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most famillar with SQL Server I will use that as my samples.
To create a stored procedure the syntax is fairly simple:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
So for example:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL Injection.
Stored procedures do come with downsides, basically the maintaince associated with your basic CRUD operation. Let's say for each table you have an Insert, Update, Delete and at least one select based on the Primary key, that means each table will have 4 procedures. Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probally will.
This is where using an ORM or some other method to auto generate your basic CRUD operations has a ton of merit.
JoshBerke
2009-01-19 22:57:15