Home > DeveloperSection > Articles > Stored Procedure in SQL

Stored Procedure in SQL


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

Stored Procedure in SQL

A stored procedure is a precompiled SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. A stored procedure is precompiled program that stored in SQL server.

Basically there are two types of stored procedure first is System Stored Procedure and second one is User Define Stored procedure.

System Stored Procedure:

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures.

System stored procedure is those stored procedure which is available by the SQL server such as for renaming table name there is one stored procedure namely SP_RENAME , so such types of stored procedure are called system stored procedure.

User Define Stored Procedure:

User Define stored procedure are those procedure which are developed by user for obtaining particular goal of task. The user are restrict to create stored procedure in master database with prefix name sp of stored procedure, because sp prefix name is used for system stored procedure in master database.

Creating User Define Stored Procedure:

Syntax:

-----------SYNTAX DEMONSTRATION OF CREATING USER DEFINE STORED PROCEDURE

CREATE PROCEDURE <PROCEDURE_NAME>

(

  ---- PARAMETER OF STORED PROCEDURE

)

AS

BEGIN

---------PROCEDURE BODY

---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE

END

GO

Example: CREATING STORED PROCEDURE

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <ARUN SINGH>

-- Create date: <9 JULY, 2011>

-- Description:   <TEST STORED PROCEDURE >

-- =============================================

CREATE PROCEDURE TEST_PROCEDURE

      -- Add the parameters for the stored procedure here

      @ID INT,

      @NAME VARCHAR(50)

 

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

 

    -- SELECT statements for procedure here

      SELECT * FROM userlogininfo

END

GO

Syntax: Altering Stored Procedure

-----SYNTAX DEMONSTRATION OF ALTERING STORED PROCEDURE

ALTER PROCEDURE <PROCEDURE_NAME>

(

  ---- PARAMETER OF STORED PROCEDURE

  @PARAMETER_NAME1 DATATYPES,

  @PARAMETER_NAME2 DATATYPES,

  ........        .......

  .......         .......

)

AS

BEGIN

      -----STORED PROCEDURE BODY

      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND

END

GO

Example: Altering Stored Procedure

USE [ArunSingh]

GO

/****** Object:  StoredProcedure [dbo].[USP_UPDATE]    Script Date: 07/11/2011 13:43:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <ARUN SINGH>

-- Create date: <30 JUNE 2011>

-- Description:   <UPDATE RECORD INTO DATABASE>

-- =============================================

ALTER PROCEDURE [dbo].[USP_UPDATE]

      -- Add the parameters for the stored procedure here

      @ID VARCHAR(50),

      @NAME VARCHAR(50),

      @ADDRESS VARCHAR(50),

      @AGE INT,

      @EMAILID VARCHAR(50)

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

 

    -- Update statements for procedure here

    UPDATE ClientInfo SET Name=@NAME,Address=@ADDRESS,Age=@AGE,EmailId= @EMAILID,[send] = 'false' WHERE ID = @ID

     

END

Syntax: Drop Stored Procedure

-----DROP DEMONSTRATION OF STORED PROCEDURE

DROP PROCEDURE <PROCEDURE_NAME>

Example:   Drop Stored Procedure

-----DROP DEMONSTRATION OF STORED PROCEDURE

DROP PROCEDURE <PROCEDURE_NAME>

 

DROP PROCEDURE TEST_PROCEDURE

User Define stored procedure can be categorized into following category:

·        Non-Parameterized Stored Procedure

·        Parameterized Stored Procedure

·        Procedure with output parameter 

Non-Parameterized Stored Procedure:

Non-Parameterized stored procedure is those stored procedure which not accept any input parameter.

Syntax: Creating Non-Parameterized Stored Procedure

---- SYNTAX DEMONSTRATION OF CREATING NON-PARAMETERIZED STORED PROCEDURE

CREATE PROCEDURE <PROCEDURE_NAME>

AS

BEGIN

 -----SQL QUERY TO PERFORM ACTION OF STORED PROCEDURE

END

GO

Example:

--- DEMONSTRATION OF NON-PARAMETERIZED STORED PARAMETER

CREATE PROCEDURE TEST_NONPARAPROCEDURE

AS

BEGIN

      SELECT * FROM userlogininfo

END

GO

Execute Non-Parameterized Procedure:

EXEC TEST_NONPARAPROCEDURE

Parameterized Stored Procedure:

Parameterized stored procedure is that procedure which accepts some parameter and performs some action on the basis of that parameter.

Syntax: Creating Parameterized Stored Procedure

-----SYNTAX DEMONSTRATION OF CREATING PARAMETERIZED STORED PROCEDURE

CREATE PROCEDURE <PROCEDURE_NAME>

(

  ---- PARAMETER OF STORED PROCEDURE

  @PARAMETER_NAME1 DATATYPES,

  @PARAMETER_NAME2 DATATYPES,

  ........        .......

  .......         .......

)

AS

BEGIN

      -----STORED PROCEDURE BODY

      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND

END

GO

Example: Creating Parameterized Stored Procedure

-----DEMONSTRATION OF CREATING PARAMETERIZED STORED PROCEDURE

CREATE PROCEDURE TESTPARAPROCEDURE

(

@NAME VARCHAR(50),

@PASS VARCHAR(50)

)

AS

BEGIN

      SELECT * FROM userlogininfo WHERE UserName = @NAME AND Password = @PASS

END

GO   

Execute Parameterized Procedure:

EXEC TESTPARAPROCEDURE  'ARUNSINGH','123456'

Procedure with output parameter:

Stored Procedure with output parameter use to return some value from stored procedure. Output parameters are created in the same space as the input parameters, right between the procedure name and AS sections. The only difference is that they are defined with the word OUTPUT immediately afterward.

Syntax: Creating stored procedure with output parameter

-----SYNTAX DEMONSTRATION OF CREATING STORED PROCEDURE WITH OUTPUT PARAMETER

CREATE PROCEDURE <PROCEDURE_NAME>

(

  ---- PARAMETER OF STORED PROCEDURE

  @PARAMETER_NAME1 DATATYPES,

  @PARAMETER_NAME2 DATATYPES OUTPUT,

  ........        .......

  .......         .......

)

AS

BEGIN

      -----STORED PROCEDURE BODY

      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND

END

GO

Example: Creating Stored Procedure with Output Parameter

-----DEMONSTRATION OF STORED PROCEDURE WITH OUTPUT PARAMETER

ALTER PROCEDURE TESTOUTPUTPARA

(

 @INPUT INT,

 @OUTPUT INT OUTPUT

)

AS

BEGIN

SET @OUTPUT = @INPUT+ 1

 

END

GO

Execute: Stored Procedure with output parameter

DECLARE @RESULT INT

EXEC TESTOUTPUTPARA'10' , @RESULT OUT

SELECT @RESULT

X

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

Follow MindStick