--- title: "Stored Procedure in SQL" description: "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 wou" author: "Anonymous User" published: 2011-07-15 updated: 2019-05-30 canonical: https://www.mindstick.com/articles/567/stored-procedure-in-sql category: "database" tags: ["database"] reading_time: 5 minutes --- # 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 (  ---- PARAMETER OF STORED PROCEDURE )ASBEGIN ---------PROCEDURE BODY ---------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HEREENDGO ``` ##### Example: CREATING STORED PROCEDURE ``` SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        -- Create date: <9 JULY, 2011>-- Description:   -- =============================================CREATE PROCEDURE TEST_PROCEDURE       -- Add the parameters for the stored procedure here      @ID INT,      @NAME VARCHAR(50)  ASBEGIN      -- 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 ENDGO ``` ##### Syntax: Altering Stored Procedure ``` -----SYNTAX DEMONSTRATION OF ALTERING STORED PROCEDUREALTER PROCEDURE (  ---- PARAMETER OF STORED PROCEDURE  @PARAMETER_NAME1 DATATYPES,  @PARAMETER_NAME2 DATATYPES,  ........        .......  .......         .......)ASBEGIN      -----STORED PROCEDURE BODY      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND ENDGO ``` ##### Example: Altering Stored Procedure ``` USE [ArunSingh]GO/****** Object:  StoredProcedure [dbo].[USP_UPDATE]    Script Date: 07/11/2011 13:43:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        -- Create date: <30 JUNE 2011>-- Description:   -- =============================================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)ASBEGIN      -- 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 PROCEDUREDROP PROCEDURE ``` ##### Example: Drop Stored Procedure ``` -----DROP DEMONSTRATION OF STORED PROCEDUREDROP PROCEDURE  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 PROCEDURECREATE PROCEDURE ASBEGIN -----SQL QUERY TO PERFORM ACTION OF STORED PROCEDUREENDGO ``` ##### Example: ``` --- DEMONSTRATION OF NON-PARAMETERIZED STORED PARAMETERCREATE PROCEDURE TEST_NONPARAPROCEDUREASBEGIN      SELECT * FROM userlogininfoENDGO ``` ##### 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 PROCEDURECREATE PROCEDURE (  ---- PARAMETER OF STORED PROCEDURE  @PARAMETER_NAME1 DATATYPES,  @PARAMETER_NAME2 DATATYPES,  ........        .......  .......         .......)ASBEGIN      -----STORED PROCEDURE BODY      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND ENDGO ``` ##### Example: Creating Parameterized Stored Procedure ``` -----DEMONSTRATION OF CREATING PARAMETERIZED STORED PROCEDURE CREATE PROCEDURE TESTPARAPROCEDURE(@NAME VARCHAR(50),@PASS VARCHAR(50))ASBEGIN      SELECT * FROM userlogininfo WHERE UserName = @NAME AND Password = @PASSENDGO    ``` ##### 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 PARAMETERCREATE PROCEDURE (  ---- PARAMETER OF STORED PROCEDURE  @PARAMETER_NAME1 DATATYPES,  @PARAMETER_NAME2 DATATYPES OUTPUT,  ........        .......  .......         .......)ASBEGIN      -----STORED PROCEDURE BODY      ---- WRITE INSERT, UPDATE, DELETE, SELECT ETC. COMMAND ENDGO ``` ##### Example: Creating Stored Procedure with Output Parameter ``` -----DEMONSTRATION OF STORED PROCEDURE WITH OUTPUT PARAMETERALTER PROCEDURE TESTOUTPUTPARA( @INPUT INT, @OUTPUT INT OUTPUT)ASBEGINSET @OUTPUT = @INPUT+ 1  ENDGO ``` ##### Execute: Stored Procedure with output parameter ``` DECLARE @RESULT INTEXEC TESTOUTPUTPARA'10' , @RESULT OUTSELECT @RESULT ``` \ X --- Original Source: https://www.mindstick.com/articles/567/stored-procedure-in-sql Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.