What is a stored procedure

Question

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

User · Answer

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    Providing a controlled access to data  end users can only enter or change data  but can t write procedures  Ensuring data integrity  data would be entered in a consistent manner  and Improves productivity  the statements of a stored procedure need to be written only once

User · Answer

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 · Answer

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

User · Answer

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  gt  p1 END REPEAT  END

User · Answer

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 · Answer

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      number as per this     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    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    CREATE PROCEDURE with the syntax for number Numbered Stored Procedures in SQL Server - techie-friendly blogspot com Grouping Stored Procedures - sqlmag   CAUTION   After you group the procedures  you can t drop them individually  This feature may be removed in a future version of Microsoft SQL Server

User · Answer

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

User · Answer

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

User · Answer

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

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  lt owner gt   lt procedure name gt         lt Param gt   lt datatype gt   AS        lt Body gt    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

User · Answer

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

User · Answer

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  lt owner gt   lt procedure name gt         lt Param gt   lt datatype gt   AS        lt Body gt    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

User · Answer

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

User · Answer

A stored procedure is nothing but a group of SQL statements compiled into a single execution plan    Create once time and call it n number of times 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

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

A stored procedure is nothing but a group of SQL statements compiled into a single execution plan    Create once time and call it n number of times 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

User · Answer

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    gt  create procedure HumanResources uspFindEmployee       lt  lt  lt ---Store procedure name    businessEntityID                                       lt  lt  lt ----parameter  as begin SET NOCOUNT ON  Select businessEntityId                lt  lt  lt ----select statement to return one employee row NationalIdNumber  LoginID  JobTitle  HireData  From HumanResources Employee where businessEntityId   businessEntityId      lt  lt  lt ---parameter used as criteria end   I learned this from essential com   it is very useful

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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  lt owner gt   lt procedure name gt         lt Param gt   lt datatype gt   AS        lt Body gt    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

User · Answer

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  lt owner gt   lt procedure name gt         lt Param gt   lt datatype gt   AS        lt Body gt    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

User · Answer

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

User · Answer

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    gt  create procedure HumanResources uspFindEmployee       lt  lt  lt ---Store procedure name    businessEntityID                                       lt  lt  lt ----parameter  as begin SET NOCOUNT ON  Select businessEntityId                lt  lt  lt ----select statement to return one employee row NationalIdNumber  LoginID  JobTitle  HireData  From HumanResources Employee where businessEntityId   businessEntityId      lt  lt  lt ---parameter used as criteria end   I learned this from essential com   it is very useful

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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    Providing a controlled access to data  end users can only enter or change data  but can t write procedures  Ensuring data integrity  data would be entered in a consistent manner  and Improves productivity  the statements of a stored procedure need to be written only once

User · Answer

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

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

User · Answer

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      number as per this     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    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    CREATE PROCEDURE with the syntax for number Numbered Stored Procedures in SQL Server - techie-friendly blogspot com Grouping Stored Procedures - sqlmag   CAUTION   After you group the procedures  you can t drop them individually  This feature may be removed in a future version of Microsoft SQL Server

User · Answer

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  gt  p1 END REPEAT  END

User · Answer

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

[sql] What is a stored procedure?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures