What is a stored procedure?

Asked at 2017-01-18 18:05:55Z
  • 5 Subscribers
  • 114 Views
126

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)?


11 answers in total

122
JoshBerke Posted at 2009-01-19 22:57:15Z

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.

70
sidhewsar Posted at 2017-01-18 19:14:58Z

A stored procedure is a set of precompiled SQL statements that are used to perform a special task.

Example: If I have an Employee table

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

First I am retrieving the Employee table:

Create Procedure Employee details
As
Begin
    Select * from Employee
End

To run the procedure on SQL Server:

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

Then second, I am inserting the value into the Employee Table

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

To run the parametrized procedure on SQL Server:

Execute employee_insert 003,’xyz’,27,1234567890

  --(Parameter size must be same as declared column size)

Example: @Name Varchar(30)

In the Employee table the Name column's size must be varchar(30).

30
Kanwar Singh Posted at 2017-01-19 06:20:02Z

A stored procedure is a group of SQL statements that has been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. A stored procedures will reduce network traffic and increase the performance. If we modify a stored procedure all the clients will get the updated stored procedure.

Sample of creating a stored procedure

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

Advantages of using stored procedures

  • A stored procedure allows modular programming.

    You can create the procedure once, store it in the database, and call it any number of times in your program.

  • A stored procedure allows faster execution.

    If the operation requires a large amount of SQL code that is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in a memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use, resulting in much faster execution times.

  • A stored procedure can reduce network traffic.

    An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

  • Stored procedures provide better security to your data

    Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

    In SQL Server we have different types of stored procedures:

    • System stored procedures
    • User-defined stored procedures
    • Extended stored Procedures
  • System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables

    Example: sp_helptext [StoredProcedure_Name]

  • User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database.

  • Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are depreciated for the reason it would be better to avoid using extended stored procedures.

13
Dave Swersky Posted at 2009-01-19 22:45:29Z

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.

4
MAA Posted at 2017-01-19 06:01:52Z

A stored procedure is mainly used to perform certain tasks on a database. For example

  • Get database result sets from some business logic on data.
  • Execute multiple database operations in a single call.
  • Used to migrate data from one table to another table.
  • Can be called for other programming languages, like Java.
2
venkat Posted at 2017-01-19 06:02:41Z

A stored procedure is used to retrieve data, modify data, and delete data in database table. You don't need to write a whole SQL command each time you want to insert, update or delete data in an SQL database.

2
Supun Wijerathne Posted at 2017-01-19 06:31:47Z

Think of a situation like this,

  • You have a database with data.
  • There are a number of different applications needed to access that central database, and in the future some new applications too.
  • If you are going to insert the inline database queries to access the central database, inside each application's code individually, then probably you have to duplicate the same query again and again inside different applications' code.
  • In that kind of a situation, you can use stored procedures (SPs). With stored procedures, you are writing number of common queries (procedures) and store them with the central database.
  • Now the duplication of work will never happen as before and the data access and the maintenance will be done centrally.

NOTE:

  • In the above situation, you may wonder "Why cannot we introduce a central data access server to interact with all the applications? Yes. That will be a possible alternative. But,
  • The main advantage with SPs over that approach is, unlike your data-access-code with inline queries, SPs are pre-compiled statements, so they will execute faster. And communication costs (over networks) will be at a minimum.
  • Opposite to that, SPs will add some more load to the database server. If that would be a concern according to the situation, a centralized data access server with inline queries will be a better choice.
1
user5723819 Posted at 2016-10-01 08:28:18Z

A stored procedure is nothing but a group of SQL statements compiled into a single execution plan.

  1. Create once time and call it n number of times
  2. It reduces the network traffic

Example: creating a stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;

      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees 
      WHERE EmployeeID = @EmployeeID
END
GO

Alter or modify a stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FirstName, LastName, BirthDate, City, Country
    FROM Employees 
    WHERE EmployeeID = @EmployeeID
END
GO

Drop or delete a stored procedure:

DROP PROCEDURE GetEmployee
1
Nirmala Hansdak Posted at 2017-01-19 06:03:25Z

A stored procedure is a named collection of SQL statements and procedural logic i.e, compiled, verified and stored in the server database. A stored procedure is typically treated like other database objects and controlled through server security mechanism.

0
Kedarnath M S Posted at 2017-01-19 06:32:57Z
  • A stored procedure is a precompiled set of one or more SQL statements which perform some specific task.

  • A stored procedure should be executed stand alone using EXEC

  • A stored procedure can return multiple parameters

  • A stored procedure can be used to implement transact

0
Naina Posted at 2017-01-19 06:33:57Z

In a DBMS, a stored procedure is a set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.

The use of a stored procedure can be helpful in

  1. Providing a controlled access to data (end users can only enter or change data, but can't write procedures)

  2. Ensuring data integrity (data would be entered in a consistent manner) and

  3. Improves productivity (the statements of a stored procedure need to be written only once)

Answer this questsion