存储过程和游标联合使用

[liyaohui13098452233]

表结构如下：


CREATE TABLE [dbo].[shuang] (
[id] [int] NOT NULL ,
[Number1] [int] NOT NULL ,
[number2] [int] NOT NULL ,
[lan] [int] NOT NULL ,
[newid] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[shuang] WITH NOCHECK ADD
CONSTRAINT [PK_shuang] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

数据如下：
id number1 number2 lan newid
2003001 10 11 11 1
2003002 4 9 12 2
2003003 1 7 16 3
2003004 4 6 3 4
2003005 4 6 16 5
2003006 1 3 6 6
2003007 1 9 7 7
2003008 5 8 8 8
2003009 5 9 9 9
2003010 1 2 13 10
2003011 4 5 15 11
2003012 2 12 12 12
2003013 8 13 12 13
2003014 3 5 2 14
要求如下：有一输入参数，值为16，我要显示lan=16 的下一条记录,最终输出的数据为：
2003004 4 6 3 4
2003006 1 3 6 6
请问sql语句如何写？其中newid,id值是不连续的



要求写一个存储过程，但是要在存储过程是使用游标

[nightmaple]

create proc aa
	@int int
as
	CREATE TABLE #result (
	[id] [int] NOT NULL ,
	[Number1] [int] NOT NULL ,
	[number2] [int] NOT NULL ,  
	[lan] [int] NOT NULL ,  
	[newid] [int] NOT NULL )
	
	declare cursor_B cursor
	read_only
	for select id,number1,number2,lan,newid from tablename
	open cursor_B
	declare @id int,@number1 int,@number2 int,@lan int,@newid int,@flag int
	fetch next from cursor_B into @id,@number1,@number2,@lan,@newid
	
	while (@@fetch_status=0)
	begin
		if @lan=@int then
			set @flag=1
		if @flag=1 then
		begin
			insert into #result values(@id,@number1,@number2,@lan,@newid)
			set @flag=0
		end
		fetch next from cursor_B into @id,@number1,@number2,@lan,@newid
	end	
	close cursor_B
	deallocate cursor_B
	
	select * from #result
return
go

exec aa 16
go

[nightmaple]

有个地方写反了。。。囧~~~

create proc aa
	@int int
as
	CREATE TABLE #result (
	[id] [int] NOT NULL ,
	[Number1] [int] NOT NULL ,
	[number2] [int] NOT NULL ,  
	[lan] [int] NOT NULL ,  
	[newid] [int] NOT NULL )
	
	declare cursor_B cursor
	read_only
	for select id,number1,number2,lan,newid from tablename
	open cursor_B
	declare @id int,@number1 int,@number2 int,@lan int,@newid int,@flag int
	fetch next from cursor_B into @id,@number1,@number2,@lan,@newid
	
	while (@@fetch_status=0)
	begin
		if @flag=1 then
		begin
			insert into #result values(@id,@number1,@number2,@lan,@newid)
			set @flag=0
		end
		if @lan=@int then
			set @flag=1
		fetch next from cursor_B into @id,@number1,@number2,@lan,@newid
	end	
	close cursor_B
	deallocate cursor_B
	
	select * from #result
return
go

exec aa 16
go

[mssql_sf]

-- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(id int,number1 int,number2 int,lan int,newid int)
Go
Insert into ta
select 2003001,10,11,11,1 union all
select 2003002,4,9,12,2 union all
select 2003003,1,7,16,3 union all
select 2003004,4,6,3,4 union all
select 2003005,4,6,16,5 union all
select 2003006,1,3,6,6 union all
select 2003007,1,9,7,7 union all
select 2003008,5,8,8,8 union all
select 2003009,5,9,9,9 union all
select 2003010,1,2,13,10 union all
select 2003011,4,5,15,11 union all
select 2003012,2,12,12,12 union all
select 2003013,8,13,12,13 union all
select 2003014,3,5,2,14 
Go

declare @id int,@number1 int,@number2 int, @lan int,@newid int
declare @b bit
declare cur cursor for select * from ta
open cur
fetch cur into @id ,@number1 ,@number2 , @lan ,@newid 

while @@fetch_status = 0
BEGIN
   if @b = 1 
   begin
       set @b = 0
       print ltrim(@id)+'  '+ltrim(@number1)+'  '+ltrim(@number2) + '   '+ltrim(@lan)+'  '+ltrim(@newid)
   end 
   else
   if @lan = 16 set @b = 1
   
   FETCH cur  into @id ,@number1 ,@number2 , @lan ,@newid 

END
CLOSE cur
DEALLOCATE cur


 
/*
2003004  4  6   3  4
2003006  1  3   6  6


*/
 

[永生天地]

这个题怎么好像有人问过了，但是没说要写存储过程。

写存储过程要简单多了

[liyaohui13098452233]

是的，我就是看了他的问题，想到了游标，所以才想使用存储过程加游标，联合使用

[xyj052]

诶，楼上的都回答了

[liyaohui13098452233]

[Quote=引用 1 楼 nightmaple 的回复:]
SQL code
create proc aa
@int int
as
CREATE TABLE #result (
[id] [int] NOT NULL ,
[Number1] [int] NOT NULL ,
[number2] [int] NOT NULL ,
[lan] [int] NOT NULL ,
[……
[/Quote]


很厉害

[salrookie11544]

学习！