What is a stored procedure?

George_S_16587 0
Viewed 574912

What is a "stored procedure" and how do they work?

What is the make-up of a stored procedure (things each must have to be a stored procedure)?

stored-procedures tsql sql-server sql
17 Answers

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 familiar 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 maintenance 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 probably will.

This is where using an ORM or some other method to auto generate your basic CRUD operations has a ton of merit.

Josh_261600

Generally, a stored procedure is a "SQL Function." They have:

-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE 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.

user_347960

Stored Procedure will help you to make code in server.You can pass parameters and find output.

create procedure_name (para1 int,para2 decimal)
as
select * from TableName
user_93239470

In Stored Procedures statements are written only once and reduces network traffic between clients and servers. We can also avoid Sql Injection Attacks.

  • Incase if you are using a third party program in your application for processing payments, here database should only expose the information it needed and activity that this third party has been authorized, by this we can achieve data confidentiality by setting permissions using Stored Procedures.
  • The updation of table should only done to the table it is targeting but it shouldn't update any other table, by which we can achieve data integrity using transaction processing and error handling.
  • If you want to return one or more items with a data type then it is better to use an output parameter.
  • In Stored Procedures, we use an output parameter for anything that needs to be returned. If you want to return only one item with only an integer data type then better use a return value. Actually the return value is only to inform success or failure of the Stored Procedure.
user_90692810

Preface: In 1992 the SQL92 standard was created and was popularised by the Firebase DB. This standard introduced the 'Stored Procedure'.

** Passthrough Query: A string (normally concatenated programatically) that evaluates to a syntactically correct SQL statement, normally generated at the server tier (in languages such as PHP, Python, PERL etc). These statements are then passed onto the database. **

** Trigger: a piece of code designed to fire in response to a database event (typically a DML event) often used for enforcing data integrity. **

The best way to explain what a stored procedure is, is to explain the legacy way of executing DB logic (ie not using a Stored Procedure).

The legacy way of creating systems was to use a 'Passthrough Query' and possibly have triggers in the DB. Pretty much anyone who doesn't use Stored Procedures uses a thing call a 'Passthrough Query'

With the modern convention of Stored Procedures, triggers became legacy along with 'Passthrough Queries'.

The advantages of stored procedures are:

  1. They can be cached as the physical text of the Stored Procedure never changes.
  2. They have built in mechanisms against malicious SQL injection.
  3. Only the parameters need be checked for malicious SQL injection saving a lot of processor overhead.
  4. Most modern database engines actually compile Stored Procedures.
  5. They increase the degree of abstraction between tiers.
  6. They occur in the same process as the database allowing for greater optimisation and throughput.
  7. The entire workflow of the back end can be tested without client side code. (for example the Execute command in Transact SQL or the CALL command in MySQL).
  8. They can be used to enhance security because they can be leveraged to disallow the database to be accessed in a way that is inconsistent with how the system is designed to work. This is done through the database user permission mechanism. For example you can give users privileges only to EXECUTE Stored Procedures rather that SELECT, UPDATE etc privileges.
  9. No need for the DML layer associated with triggers. ** Using so much as one trigger, opens up a DML layer which is very processor intensive **

In summary when creating a new SQL database system there is no good excuse to use Passthrough Queries.

It is also noteworthy to mention that it is perfectly safe to use Stored Procedures in legacy systems that already uses triggers or Passthrough Queries; meaning that migration from legacy to Stored Procedures is very easy and such migration need not take a system down for long if at all.

user_22885800
Related