blog

Home / DeveloperSection / Blogs / Stored Procedure in SQL Server

Stored Procedure in SQL Server

Stored Procedure in SQL Server

Anonymous User 6255 07-Sep-2011

The stored procedure is an important concept that is used for access or modifies data into the database. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. Stored procedures are special objects available in the SQL server. It’s a precompiled statement where all the preliminary parsing operations are performed and the statements are ready for execution. It’s very fast when compared to ordinary SQL statements where the SQL statements will undergo a sequence of steps to fetch the data.

Stored procedures are modules or routines that encapsulate code for reuse. A stored procedure can take input parameters, return tabular or scalar results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters.

Syntax: Creating a Stored Procedure
Create Procedure [Procedure Name]
(
--stored Procedure parameter name
)
As
Begin
 --stored procedure body code
 --update, insert, delete, select command etc.
End
1.
2.
3.
4.
5.
6.
7.
8.
9.

Example: Creating a Stored Procedure

CREATE PROCEDURE TESTPROCEDURE 
      -- Add the parameters for the stored procedure here
      @PASSWORD VARCHAR(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    -- Insert statements for procedure here
      SELECT * FROM userlogininfo WHERE Password = @PASSWORD
END
GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

// OUTPUT: EXEC TESTPROCEDURE '123456'
Desired Output: 102      Arunsingh           Vashundhra Ghaziabad               

Example: Modifying Stored Procedure
ALTER PROCEDURE [dbo].[TESTPROCEDURE] 
      -- Add the parameters for the stored procedure here
      @Username VARCHAR(50)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    -- Insert statements for procedure here
      SELECT * FROM userlogininfo WHERE UserName = @Username
END
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

// Output: exec [dbo].[TESTPROCEDURE] 'arunsingh'
Desired Output: 102  Arunsingh  Vashundhra Ghaziabad               

Example: Drop Stored Procedure

drop procedure [dbo].[TESTPROCEDURE]
1.
Types of Stored Procedure:

There are many types of stored procedure in SQL server in which some are defined as follows:

User Define Stored Procedure: User-defined stored procedure are those stored procedures which are created by you and perform a specific task. Generally, User Define Stored procedures are two types which are namely as follows:

1.  Transact-SQL stored procedure:  A Transact-SQL stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters. For example, a stored procedure might contain the statements needed to insert a new row into one or more tables based on information supplied by the client application. Or, the stored procedure might return data from the database to the client application.


2.   Common Language Runtime (CLR): In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure.

Extended Stored Procedure:  Extended stored procedures let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

System Stored Procedure: Many administrative activities in SQL Server are performed through a special kind of procedure known as a system stored procedure. For example, SYS.SP_CHANGEDBOWNER is a system stored procedure. System stored procedures are physically stored in the resource database and have the sp_ prefix. System stored procedures logically appear in the sys schema of every system- and user-defined database.


Updated 16-Oct-2019
I am a content writter !

Leave Comment

Comments

Liked By