Stored Procedure -SQL Server

Stored Procedure is most popular database object. A stored procedure in SQL Server is a group of one or more Transact-SQL statements  . Procedures resemble constructs in other programming languages because they can:

ü  Allow input parameters and return multiple values in the form of output parameters to the calling program.

ü  Contain programming statements that perform operations in the database. Can calling other procedure, functions

ü  Return a status value to a calling program to indicate success or failure (and the reason for failure).

Benefits of Using Stored Procedures:

       

ü  Reduced server/client network traffic: A procedure contain a batch of sql statement, even execute other procedure, This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network.

ü  Stronger security: Store procedure has stronger permission, administrator has grant option to give access procedure and also underlying object. When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data. Procedures can be encrypted.

ü  Improved performance:

Types of Stored Procedures:

ü  User-defined: Permitted  db user can create store procedure for their own  business.

ü  Temporary: Permitted  db user can create temporary procedure, temporary proc stored in tempDB. There are two types of temporary procedures: local and global.

ü  System: System procedures are included with SQL Server.the msdb database also contains system stored procedures in the dbo schema . many administrative and informational activities can be performed by using system stored procedures

ü  Extended User-Defined:  Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures will be removed in a future version of SQL Server

Create Procedure:

-- =============================================
-- Author: http://www.code-lake.blogspot.com/
-- Create date: 2016-Nov-16
-- Description: Get Customer-wise order details
-- EXEC CustomerOrders 'TOMSP'
-- =============================================
CREATE PROCEDURE CustomerOrders
 @CustomerID nchar(5)
AS
BEGIN
   SELECT OrderID,OrderDate,RequiredDate,ShippedDate
     FROM Orders
    WHERE CustomerID = @CustomerID
 ORDER BY OrderID
END

To call or execute procedure:

EXEC CustomerOrders 'TOMSP'