STORE PROCEDURE USING SQL SERVER

DEFINATION:A stored procedure is a collection of compiled T-SQL commands that are directly accessible by SQL Server. 

Syntax:
Create PROC/Procedure Procedure_Name
@parameter _name1 datatype,@parameter _name2 datatype,----@parameter _namen datatype,
AS
BEGIN
Procedure of Statement
END

-----------Create a Database and Table---------------------

create  database storeprocedure

use storeprocedure

create table sunil(id int primary key,Name varchar(50),Address varchar(50))

insert into sunil values(1,'naveen','Noida'),(2,'bharti','Patna'),(3,'Shivam','Kasi'),(4,'vichitra','Kanpur')

select * from sunil

---------------------------------Create Procedure For Insert-------------------------

Create procedure InsertStore

@Id int ,@Name varchar(50),@Address varchar(50)

as 

begin

insert into sunil values(@id,@name,@Address)

end

  Execute insertStore 7,'punit','bihar'

---------Create Procedure For Delete-----

Create Procedure DeleteStore

@id int

as 

begin

delete from sunil where id=@id

end

execute DeleteStore 5

---------Create Procedure For Update------

Create Proc UpdateStore

@id int,@Name Varchar(50),@Address varchar(50)

as 

begin

update sunil set Name=@Name,Address=@Address where id=@id

end

execute UpdateStore 5, 'punit','Bihar'

--------Create Procedure For Select-----

Create proc SelectStore

@id int

as 

begin

select * from sunil

end

execute SelectStore 1

------------Create TRY And CATCH--------------------

BEGIN 

TRY

SELECT 202/0

END 

TRY

BEGIN

 CATCH

SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(),

ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()

END

 CATCH