if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[result]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[result] GOdrop proc find_sno gocreate table result (sno varchar(8) not null, sname varchar(8) not null, ) go create proc find_sno(@sno_v varchar(8)) as declare @num int, @v_sno varchar(8), @v_sname varchar(8), @v_cno varchar(8), @flag int, @looptime1 int, @looptime2 int declare cur_sno cursor for select sno,sname from student declare cur_cno cursor for select cno from sc where sno=@sno_vset @looptime1=(select count(*) from student)begin open cur_sno set @flag=0 while @looptime1!=0 begin fetch next from cur_sno into @v_sno,@v_sname set @looptime1=@looptime1-1 open cur_cno set @looptime2=(select count(*) from sc where sno=@sno_v) while @looptime2!=0 begin
set @num=0 fetch next from cur_cno into @v_cno set @num=(select count(*) from sc where cno=@v_cno and sno=@v_sno) if @num=0 set @flag=1
set @looptime2=@looptime2-1
end close cur_cno
if @flag=0 insert into result values(@v_sno,@v_sname) end close cur_sno DEALLOCATE cur_sno DEALLOCATE cur_cno end go execute find_sno 95001 go select * from result;
CREATE PROCEDURE test
@a int
AS
Select * From 表
1、牢记定义
2、多写、多练、多问
3、先写结构、再添业务逻辑代码
4、封装一些公用的存储过程,尽量复用
5、注释清晰
6、经常问csdn中的老大们(他们很厉害的)!
如:
/*******************************************************
名称: up_Trim
功能: 滤掉左右两边的空格
参数: @vchParam 待过滤的字符串
返回: 过滤后的字符串
作者: 赵杰
日期: 2004-04-06
********************************************************/
CREATE procedure dbo.up_Trim @vchParam varchar(8000) output
as
begin
select @vchParam = LTrim( RTrim( @vchParam ) )
end
drop table [dbo].[result]
GOdrop proc find_sno
gocreate table result
(sno varchar(8) not null,
sname varchar(8) not null,
)
go
create proc find_sno(@sno_v varchar(8))
as
declare @num int,
@v_sno varchar(8),
@v_sname varchar(8),
@v_cno varchar(8),
@flag int,
@looptime1 int,
@looptime2 int
declare cur_sno cursor for
select sno,sname from student
declare cur_cno cursor for
select cno from sc where sno=@sno_vset @looptime1=(select count(*) from student)begin
open cur_sno
set @flag=0
while @looptime1!=0
begin
fetch next from cur_sno into @v_sno,@v_sname
set @looptime1=@looptime1-1
open cur_cno
set @looptime2=(select count(*) from sc where sno=@sno_v)
while @looptime2!=0
begin
set @num=0
fetch next from cur_cno
into @v_cno
set @num=(select count(*) from sc where cno=@v_cno and sno=@v_sno)
if @num=0
set @flag=1
set @looptime2=@looptime2-1
end
close cur_cno
if @flag=0
insert into result values(@v_sno,@v_sname)
end
close cur_sno
DEALLOCATE cur_sno
DEALLOCATE cur_cno
end
go
execute find_sno 95001
go
select * from result;