Login

for simple,

**Stored Procedure** are **Stored Programs**, A program/function stored into database.

Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;) characters.

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

Stored procedures in SQL Server can accept input parameters and return multiple values of output parameters; in SQL Server, stored procedures program statements to perform operations in the database and return a status value to a calling procedure or batch.

The benefits of using stored procedures in SQL Server

They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.

Here is an example of a stored procedure that takes a parameter, executes a query and return a result. Specifically, the stored procedure accepts the BusinessEntityID as a parameter and uses this to match the primary key of the HumanResources.Employee table to return the requested employee.

> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end

I learned this from essential.com...it is very useful.

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][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][ORM] or some other method to auto generate your basic CRUD operations has a ton of merit.

[CRUD]:

[To see links please register here]

[ORM]:

[To see links please register here]

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

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 deprecated for the reason it would be better to avoid using extended stored procedures.

"What is a stored procedure" is already answered in other posts here. What I will post is one less known way of using stored procedure. It is `grouping stored procedures` or `numbering stored procedures`.

**Syntax Reference**

[![enter image description here][1]][1]

`; number` as per [this](

[To see links please register here]

)

>An optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement

**Example**

CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO

CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO

**Use**

exec FirstTest 10
exec FirstTest;2 20,30

**Result**

[![enter image description here][2]][2]

**Another Attempt**

CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO

**Result**

> Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3]
Cannot create procedure 'SecondTest' with a group number of 2 because a procedure with the same name and a group number of 1 does not currently exist in the database.
Must execute CREATE PROCEDURE 'SecondTest';1 first.

**References**:

1. [CREATE PROCEDURE with the syntax for number](

[To see links please register here]

)
2. [Numbered Stored Procedures in SQL Server - techie-friendly.blogspot.com](

[To see links please register here]

)
3. [Grouping Stored Procedures - sqlmag](

[To see links please register here]

)

**CAUTION**

1. After you group the procedures, you can't drop them individually.
2. This feature may be removed in a future version of Microsoft SQL Server.

[1]:

[2]:

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.

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.
1. They have built in mechanisms against malicious SQL
injection.
1. Only the parameters need be checked for malicious SQL
injection saving a lot of processor overhead.
1. Most modern database
engines actually compile Stored Procedures.
1. They increase the
degree of abstraction between tiers.
1. They occur in the same
process as the database allowing for greater optimisation and
throughput.
1. 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).
1. 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.
1. 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.

```
create procedure <owner>.<procedure name><param> <datatype>
As
<body>
```

Stored procedure are groups of SQL statements that centralize data access in one point. Useful for performing multiple operations in one shot.

sonnymcnri

stenchion801639

hypodiatessaron50137

lowellmyyps

bycatches350147

ieyasu322

alfrediaalfredo123

nightshine157046

voguey76949