--- title: "Implementing Stored Procedure" description: "Stored procedures are defined as precompiled object stored in a database. Here I had mentioned precompiled object that means such type of entity which" author: "Anonymous User" published: 2011-01-27 updated: 2020-03-28 canonical: https://www.mindstick.com/articles/429/implementing-stored-procedure category: "database" tags: ["database"] reading_time: 5 minutes --- # Implementing Stored Procedure Stored procedures are defined as precompiled object stored in a database. Here I had mentioned precompiled object that means such type of entity which is compiled and after compilation the form that we get is stored in database. Stored Need of Procedures When we are talking about to develop any enterprise application then efficiency is major points that we have to mention. We want to make such type of projects which is efficient in terms of input output. When we execute any SQL script or batch more than once, you need to recreate SQL statements and submit them to the server. This process leads to increase the load upon the server in terms of compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. Stored procedures can invoke all types’ database commands such as DDL (Data Definition Language), DML (Data Manipulation Language) or DQL (Data Query Language) and can return values. ##### Types of procedure 1. Non Parameterize Procedure 2. Parameterize Procedure Non - parameterize procedure means such types of procedure which does not need values at run time which simply returns output of the query while parameterized procedure are such types of procedure which requires value at run time. ##### Syntax for creating Non-Parameterize procedure ``` create procedure/proc procedure_name asbegin     sqlstatement1     sqlstatement2end ``` where create is a keyword to create a database command procedure/proc is a keyword which tells that the object that is created is a procedure as is a keyword begin/end is a keyword which is used to create a block sqlstatement1/sqlsatement2 are the sqlstatements that you want to execute ##### Example for creating non paramentrize stored procedure ``` create procedure stuDentDetails asbegin     select * from Studentend ``` After executing these steps procedure is created and stored in a database. Now for executing the procedure type following syntax in your SQL browser. ##### Syntax for executing Procedure exec **procedure_name\** where exec is a keyword which is used to execute your procedure procedure_nameis the name of the procedure that you want to execute Example for executing non parameterize stored procedure Exec stuDentDetails Output- ![Implementing Stored Procedure](https://www.mindstick.com/mindstickarticle/37dd5217-1856-418c-84af-a344fc353e0c/images/43d3054c-4dcf-47c8-bf69-9315106778bd.png) ##### The steps that server performed to create a stored procedure - In the first phase the procedure is compiled that is syntax for creating procedure is checked and then its component are broken into various pieces. This process is known as parsing. - The existence of the referred objects such as tables and views are checked. This process of checking tables and views is known as resolving. - The name of the procedure is stored in the sysobjects tables and the code that creates the stored procedure is stored in the syscommnets table. - The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as an execution plan. - When the procedure is executed for the first time, the execution plan will be read and fully optimize and then run. ##### Creating parameterize stored procedure When we want to pass values to the procedure at run time then we have to create parameterize procedure. ##### Syntax for creating parameterized procedure create procedure/proc **procedureName parameterName datatype** as begin ## sqldatement1 ## sqlstatement2 end where procedureName - is the name of the procedure that you want to create parameteName -is the name of the parameter that you want to create. Make sure parameter name should be start with@ symbol datatype -Data type of your parameter such as varchar, char or int ##### Example for creating parameterized procedure ``` create procedure prcStudentDetails @sage intasbegin     print 'List of the student having same age'     select * from Student where sage=@sageend ``` ##### Executing parametrized procedure exec **prcStudentDetails** 22 ##### output ![Implementing Stored Procedure](https://www.mindstick.com/mindstickarticle/37dd5217-1856-418c-84af-a344fc353e0c/images/fba0d6e3-3930-4e67-8ea9-c702fec40e01.png) ##### Creating parameterize procedure by using output keywords Sometimes we need such types of procedure which returns some value in the variable. Simple procedure does not return any value. They execute only the query. Whenever we want procedures which return a value and we can use those values in different procedure than we need to create a procedure by using output keyword. To specify a parameter as the output parameter, you can use the output keyword. ##### Syntax for declaring an output parameter using the output keyword ``` create procedure procedurename @parameterName datatype outputasbegin     sqlstatement1     sqlstatement2end ``` ##### Example to demonstrate the use procdure which use output keyword ``` 1)      create procedure prcStudentOutputDemo @sid varchar(5),@sname varchar(20) output,@scity varchar(20)outputasbegin     if exists(select * from Student where sid=@sid)     begin          select @sname=sname,@scity=scity from Student wheresid=@sid          return 0     end     else          return 1end ``` ``` 2)      create procedure showDetails @sid varchar(5)asbegin     declare @returnValue int     declare @sname varchar(30)     declare @scity varchar(30)     exec @returnValue = prcStudentOutputDemo @sid,@sname output,@scity output     if(@returnValue = 0)     begin          print 'Student Name '+@sname           print 'Student City '+@scity     end     else          print 'No records found'end ``` In the first example I had created a procedure by using output key word and in second example I had created a procedure which use that procedure. This steps also known as calling one procedure inside another procedure. ##### Executing the procedure exec **showDetails** 'S0001' ##### output ![Implementing Stored Procedure](https://www.mindstick.com/mindstickarticle/37dd5217-1856-418c-84af-a344fc353e0c/images/cd3f32ca-80f9-41c3-8565-c84810135d32.png) ##### Some useful command related with procedure - sp_help gives the description of the procedure that you have created. - Sysobject By using sysobjects view you can search all the procedure that you have created. Sysobject view provides a table where all the procedure are stored. ##### Altering a stored procedure Whenever you want to modify the stored procedure then use the alter procedure statement. The syntax of alter procedure statement is \ ``` alter procedure procedureName asbegin     sqlstatement1     sqlstatement2end ``` ##### Example for altering the procedure ``` alter procedure stuDentDetails asbegin     select * from Studentend ``` ##### Droping procedure from database You can drop a procedure from a database by using drop command. The syntax for droping procedure is as follows. drop procedure **procedureName** ##### Example for droping procedure drop procedure **stuDentDetails** --- Original Source: https://www.mindstick.com/articles/429/implementing-stored-procedure Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.