Stored Procedures in SQL

Stored Procedures in SQL

Stored Procedures are set of Pre-compiled Queries . By word "Stored Procedures" we mean the set of statement that are only compiled first at time of first time they are compiled and stored and saved on SQL Server for use in future are called as Stored Procedures.



CREATE STORED PROCEDURES IN SQL

* First , Open your Microsoft SQL Server Management Studio software from Start menu as shown in image below




* Syntax for Creating Procedure :-

CREATE Procedure Procedure-Name
(
@field-Name-1  field-type
@field-Name-2  field-type

@field-Name-n  field-type 
  )


* where, CREATE Procedure is a Keyword used for creating procedures in SQL. Generally, keywords are fixed word or reserved words in Structured Query Language these have a special meaning in language desired for some specific work.

* Procedure-Name is name you want to Assign to Procedure you are creating. Procedure name can be any name except from keywords keywords can never be the name of Procedure

* These below statements are the fields of table with their their data-type defined in table. you should give only those fields in procedure round brackets ()  that are required in our procedure if you give extra fields in it then it will give error or force you to given that field also in stored query as it is not required there

@field-Name-1  field-type
@field-Name-2  field-type
@field-Name-n   field-type

EXAMPLE 1 :-   Create A Procedure For insert Query

create procedure insert_procedure
(
@rollno int,
@ename varchar(10),
@age int,
@course varchar(10)
)
as
insert into student values(@rollno,@ename,@age,@course) 

OUTPUT :-

 

 


EXECUTING PROCEDURE -- insert



Syntax    :-    exec Procedure-Name  values 

* exec is keyword used for executing procedures
* procedure-Name is general name of procedure
* values are required values for executing procedure



 












EXAMPLE 2:-   Create A Procedure For Delete Query

 CREATE Procedure del_procedure
(
@rollno int
)
as
delete from student
where rollno = @rollno


OUTPUT :-



EXECUTING PROCEDURE --     del_procedure
 
Syntax  :- exec procedure-Name value

* exec is keyword used for executing procedures
* procedure-Name is general name of procedure
* values are required values for executing procedure 















EXAMPLE 3:-   Create A Procedure For Update Query

We can do updating of data in table by writing it But we can use it only one time . For updating values one more time we have to write query one more time . But if we have created Procedure for Update Query then there is no need to write the query again and again . We can execute it directly number of times by supplying the required values.

CREATE procedure update_student
(
@rollno int,
@course varchar(10)
)
as
update student
set course = @course
where rollno = @rollno

 

EXECUTING PROCEDURE --     Update_student

Syntax  :- exec procedure-Name values

* exec is keyword used for executing procedures
* procedure-Name is general name of procedure
* values are required values for executing procedure





Share this

heemanshu bhalla