What is stored procedure? How to write stored procedure for insert update and delete records [Resolved]

stored procedure is the predefined collection of sql statements..

Its Precompiled and reduce the client server network traffic..

Each storedprocedure can have n number sql statements

store procedure easy to error handling...

1) system stored procedre
2) user defined stored procedure
3) temporary stored procedure
4) extended store procedure
5) remote stored procedure

create procedure sp_test
as
select * from test
go

using Insert:

create procedure sp_test
@empid int,
@empname varchar(90)
as
insert into test (empid,empname) values(@empid,@empname)

go

using update:

create procedure sp_update
@empname varchar(90),
@empid int
as
update test set empname=@empname where empid=@empid


hope this will help

sriram

Shweta_Pinky, if this helps please login to Mark As Answer. | Alert Moderator

hi

try this

CREATE TABLE [dbo].[z2](
[DeptID] [int] IDENTITY(1,1) NOT NULL,
[Deptname] [varchar](20) NULL,
CONSTRAINT [PK_z2] PRIMARY KEY CLUSTERED
(
[DeptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Alter proc Test_Proc1
@DeptName varchar(50),
@id int,
@Mode varchar(50)
as
IF @Mode='Insert'
begin
insert INTO z2(Deptname) VALUES(@DeptName)
end
else
IF(@Mode='Update')
begin
Update z2 set Deptname=@DeptName where DeptId=@Id
end
else
IF(@Mode='Delete')
begin
Delete from z2 where DeptId=@Id
end

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Shweta_Pinky, if this helps please login to Mark As Answer. | Alert Moderator