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