Skip to main content

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

cPanel Reseller Hosting
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'


Labels:

Comments

Post a Comment

Popular posts from this blog

Database View

View is a database object also called a logical table. it has no psychical existence. It is not like a simple table, but is a virtual or logical table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View. Creating view Syntax: ------------------------------------------------- CREATE VIEW [View_Name] AS [ SELECT Statement] ------------------------------------ ------------- CREATE VIEW SampleView As SELECT EmpID, EmpName FROM EmpInfo -------------------------------------------------- Data retrieve from view: SELECT * FROM SampleView WHERE EmpID ='FN0009C1'     View does not modif
Post a Comment
Read more

Efficiently Paging Through Large Amounts of Data (PageIndex, Page Size) -SQL Server

In this article you learn how to fetch data according PageIndex and PageSize. In web application, it is much more important to increase webform performance, loadbalance. In my development experience, some of table hold large amount of records (more than 2GB) and user need to shows records in GridView. But problem is when we select all records and loads in webforms, webform has crashed. In that case, I will simply solved with Table Variable and using Grid Page Number and Page Size. 1. Create Procedure CREATE PROCEDURE Load_Data_WithPaging @PageIndex AS INT , /*Selected Row Index of selected grid*/ @PageSize AS INT , /*Total page size of selected grid*/ @TotalRecords AS INT OUT /*used for display virtual page no*/ AS BEGIN SET NOCOUNT ON ; DECLARE @FromIndex AS INT = 0 , @ToIndex AS INT = 0 ; SET @FromIndex = (@PageIndex * @PageSize) + 1 ; /*First row no selection*/ SET @ToIndex = ((@PageIndex
Post a Comment
Read more

What is an Index? Explain Custered Index and Non-Clustered Index

Index Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML ( Insert , Delete and Update ) operations. clustered index A clustered index is something that reorganizes the way records in the table are physically stored. Therefore a table can have only one clustered index. The leaf nodes of a clustered index contain the data pages, by which I mean the key-value pair in the clustered index has the index key and the actual data value. Also remember, a clustered index will be created on a table by default the moment a primary key is created on the table. A clustered index is so
Post a Comment
Read more