<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> 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
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> /* 创建表 */ 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