How to Use Procedure

SQL give you the capability to define your own procedure, 

Here is an example. 

 

create procedure Tonytest 

--after create this Procedure, you can use alter instead of create to modify it-- 

as 

Begin 

Select student_number as pre_stdn from students 

update students set student_number=student_number+1 where student_number is not null 

--add 1 on all student numbers-- 

select student_number as alt_stdn from students 

End 

 

 

After this define, use this to run procedure: 

 

Execute Tonytest; 

 

You can see it has been changed. 

 

If you want to go check all Procedure you made, please use: 

 

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION 

from INFORMATION_SCHEMA.ROUTINES 

where ROUTINE_TYPE='PROCEDURE'