Create Stored Procedures In Shortest Way

Dotnet4ashu
Posted by Dotnet4ashu under Sql Server category on | Points: 40 | Views : 1545
Hello EveryOne,


Simple Creation of Stored Procedure in Sql 05

For Insert

First Create Table

create table tab1
(
e_id int,
e_name varchar(30),
e_address varchar(30)
}
Now Create Procedure

Create Proc PRCINSERT
(
@e_id int,
@e_name varchar(30),
@e_address varchar(30)
)
as
insert into tab1 values
(
@e_id,
@e_name,
@e_address
)
Now Execute the Above Procedure

Exec PRCINSERT 1,'a','Delhi'

If above Procedure is use primary key with identity on e_id then it shows an error of :
Msg 8101, Level 16, State 1, Procedure prinsert, Line 9
An explicit value for the identity column in table 'tab1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

And if Identity is Off In the Table
Then Command will be Successfully Completed with Primary Key

For Search

Create proc procsearch
(
@e_id int
)
as
select * from tab1 where e_id=@e_id

For Update

Create proc procupdate
(
@e_id int,
@e_name varchar(30),
@e_address varchar(30)
)
as
update emp set e_name=@e_name,e_address=@e_address where e_id=@e_id

For Delete
Create Proc PRCDELETE
(
@e_id int
)
as
delete from tab1 where e_id=@e_id

Comments or Responses

Posted by: T.Saravanan on: 2/20/2012 Level:Silver | Status: [Member] [MVP] | Points: 10
Hi,

Nice Info.
Kindly post your code inside the code tag.
Posted by: Sksamantaray on: 2/21/2012 Level:Silver | Status: [Member] | Points: 10
good one

Login to post response