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

Posted by Shweta_Pinky under Sql Server on 5/26/2013 | Points: 10 | Views : 3805 | Status : [Member] | Replies : 3
What is stored procedure? How to write stored procedure for insert update and delete records?




Responses

Posted by: Satyapriyanayak on: 5/26/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored Procedure, you actually
run a set of statements. Stored Procedure are the precompiled set of sql command.
Stored procedures means containing a precompiled block of code. if we call stored procedures they need not compiled, only execution takes place. With this advantage, work on database is less.

Example

Create table student (sid varchar(50),sname varchar(50),smarks int,saddress varchar (50),year varchar(50))


For insert records

CREATE PROCEDURE insert
(@sid varchar(50),@sname varchar(50),@smarks int,@saddress varchar (50),@year varchar(50))
AS
insert student(sid,sname,smarks,saddress,year) values (@sid,@sname,@smarks,@saddress,@year)


For update records

CREATE PROCEDURE update
(@sid varchar(50),@sname varchar(50),@smarks int,@saddress varchar (50),@year varchar(50))
AS
update student set sname=@sname,smarks=@smarks,saddress=@saddress,year=@year where sid=@sid


For delete records

CREATE PROCEDURE delete
(@sid varchar(50))
AS
delete from student where sid=@sid

If this post helps you mark it as answer
Thanks

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

Posted by: Sriramnandha on: 9/6/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Jayakumars on: 9/6/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

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

Login to post response