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
/*在存储过程中修改参数值，使用多个查询语句：*/
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
/*创建表*/
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;
/*修改数据的存储过程*/
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
/*删除数据的存储过程*/
create procedure proc_person_del
(
@id int
)
as
delete from person where id = @id
go
execute proc_person_del 1
select * from person