insert into person values(1,'zdw','zdw','test1')
insert into person values(2,'test','test','test2')
insert into person values(3,'admin','admin','admin3')
/*在存儲過程中使用子查詢*/
create procedure person_sub_query
(
@id int
)
as
select * from person where id < (select count(*) from person)
go
execute person_sub_query 2
/*在存儲過程中修改參數(shù)值，使用多個查詢語句：*/
create procedure person_multi_query
(
@id int
)
as
select @id = (select count(*) from person where id > @id)
select @id = @id - 1
select * from person where id = @id
go
execute person_multi_query 1
/*創(chuàng)建表*/
create table person
(
id int primary key,
username varchar(50) not null,
password varchar(20) not null,
address varchar(200) not null
)
/*增加一條記錄的存儲過程*/
create procedure proc_person
(
@id int ,
@username varchar(50),
@password varchar(20),
@address varchar(200)
)
as
insert into person(id,username,password,address) values(@id,@username,@password,@address)
go
/*傳值順序是你聲明變量時的順序*/
exec proc_person 1,'admin','admin','bj'
select * from person;
/*修改數(shù)據(jù)的存儲過程*/
create procedure proc_person_update
(
@id int,
@username varchar(50),
@password varchar(50)
)
as
update person set username=@username , password=@password where id=@id
go
execute proc_person_update 1,'test','test'
select * from person
/*刪除數(shù)據(jù)的存儲過程*/
create procedure proc_person_del
(
@id int
)
as
delete from person where id = @id
go
execute proc_person_del 1
select * from person