Basic concepts of Stored Procedure...

--¤ TYPE OF STORED PROCEDURE----------------------------
--(1). SYSTEM STORED PROCEDURE
--(2). USER DEFINED STORED PROCEDURE
--TYPE OF USER DEFINED STORED PROCEDURE------------------
--(1). NON PARAMETARIZED STORED PROCEDURE
--(2). PARAMETERIZED STORED PROCEDURE

--¤ USER DEFINED PROCEDURE-"NON PARAMETERIZED STORED PROCEDURE"----------------------------------------------------

create table contact(id int,name nvarchar(15),age int)
select * from contact
insert into contact select 1,'Deepak',23 union all select 2,'Shipra',23 union all select 3,'Richa',23 union all select 4,'Saumya',23
create table emp(id int,address nvarchar(max))
select * from emp
insert into emp select 1,'Gurgaon' union all select 2,'Noida' union all select 3,'Noida' union all select 3,'Gurgaon'
-------------------------------------------------------------------------------------------------------------------
create proc ProcDemo_select as select * from contact
exec ProcDemo_select
-------------------------------------------------------------------------------------------------------------------
create proc ProcDemo_join as select e.name,e.age,e1.address from contact e inner join emp e1 on e.id=e1.id
exec ProcDemo_join
-------------------------------------------------------------------------------------------------------------------

--¤ USER DEFINED PROCEDURE -"PARAMETERIZED PROCEDURE"--------------------------------------------------------------

create proc ProcDemo_insert(@id int, @name nvarchar(15), @age int) as insert into contact values(@id,@name,@age)
exec ProcDemo_insert 4,'Aryan',23
--or
--ProcDemo_insert 5,'Hello',23
exec ProcDemo_select
-------------------------------------------------------------------------------------------------------------------
create proc ProcDemo_update(@id int,@name nvarchar(15), @age int) as update contact set name=@name, age=@age where id=@id
exec ProcDemo_update 4,'Radha',23
exec ProcDemo_select
-------------------------------------------------------------------------------------------------------------------
create proc ProcDemo_delete(@id int) as delete from contact where id=@id
exec ProcDemo_delete 4
exec ProcDemo_select
--------------------------------------------------------------------------------------------------------------------
----FOR VIEW ALL OF QUERY OF STORED PROCEDURE-----------------------------------------------------------------------
/*sp_helptext ProcDemo_select
  sp_helptext ProcDemo_insert
  sp_helptext ProcDemo_update
  sp_helptext ProcDemo_delete*/
--------------------------------------------------------------------------------------------------------------------

--FOR DELETE STORED PROCEDURE---------------------------------------------------------------------------------------
drop proc ProcDemo_select
drop proc ProcDemo_insert
drop proc ProcDemo_update
drop proc ProcDemo_delete
--------------------------------------------------------------------------------------------------------------------