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
  Modified On Nov-21-2017 01:28:39 AM

Leave Comment