Home > DeveloperSection > Blogs > SQL Stored Procedure

SQL Stored Procedure


Database Database 
Ratings:
0 Comment(s)
 3975  View(s)
Rate this:

Stored Procedure:

                Stored procedure is an important concept which is used for access or modifies data into 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 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 Stored Procedure

Create Procedure [Procedure Name]

(// stored Procedure parameter name) As

Begin

// stored procedure body code

// update, insert, delete, select command etc.

End

Example: Creating 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

// OUTPUT: EXEC TESTPROCEDURE '123456'
Desired Output: 102       Arunsingh           Vashundhra Ghaziabad arunbaswar@gmail.com               123456  7607185995                Admin   1989-02-28

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

// Output: exec [dbo].[TESTPROCEDURE] 'arunsingh'
Desired Output: 102       Arunsingh           Vashundhra Ghaziabad arunbaswar@gmail.com               123456  7607185995                Admin   1989-02-28

Example: Drop Stored Procedure

drop procedure [dbo].[TESTPROCEDURE]

Types of Stored Procedure:

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

1.     User Define Stored Procedure:

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

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

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

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

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


Don't want to miss updates? Please click the below button!

Follow MindStick