declare cur cursor for... open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
if(...)
begin
...
end
fetch next from cur into ...
end至此没有错误,但是在if后面加一个else段就出错了declare cur cursor for... open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
if(...)
begin
...
end
else
begin
... --即使此处为空也报错
end
fetch next from cur into ...
end消息 156,级别 15,状态 1,
关键字 'fetch' 附近有语法错误。
消息 102,级别 15,状态 1,
'end' 附近有语法错误。
fetch next from cur into ...
while(@@FETCH_STATUS=0)
if(...)
begin
...
end
fetch next from cur into ...
end至此没有错误,但是在if后面加一个else段就出错了declare cur cursor for... open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
if(...)
begin
...
end
else
begin
... --即使此处为空也报错
end
fetch next from cur into ...
end消息 156,级别 15,状态 1,
关键字 'fetch' 附近有语法错误。
消息 102,级别 15,状态 1,
'end' 附近有语法错误。
--begin end 中必醃有語句塊(肯定報錯)
declare cur cursor for... open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
begin
if(...)
begin
...
end
else
begin
...
end
fetch next from cur into ...
end
declare cur cursor for... open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
begin
if(...)
begin
...
end
else
begin
... --即使此处为空也报错
end
fetch next from cur into ...
end
close cur
deallocate cur
begin if(...)
begin
...
end
else
begin
... --即使此处为空也报错
end
fetch next from cur into ...
end
declare cur cursor for...
open cur
fetch next from cur into ...
while(@@FETCH_STATUS=0)
begin
if(...)
begin
...
end
else
begin
... --即使此处为空也报错
end
fetch next from cur into ...
end
drop table student
go
create table student(
id int ,
name varchar(10),
gender int check(gender=1 or gender=2)
)
go
insert into student values(1001,'tracy',1)
insert into student values(1002,'lily',2)
insert into student values(1003,'kobe',1)
insert into student values(1004,'lucy',2)
insert into student values(1005,'nash',1)
godeclare cur cursor for select *from student
declare @id int,@name varchar(10),@gender int
open cur
fetch next from cur into @id,@name,@gender
while @@fetch_status=0
begin
if @id=1001 or @id=1002
begin
print ltrim(str(@id))+','+@name+','+ltrim(str(@gender))
fetch next from cur into @id,@name,@gender
print' hello'
end
else
if @id=1003
begin
print ltrim(str(@id))+','+@name+','+ltrim(str(@gender))
fetch next from cur into @id,@name,@gender
print' nihao'
end
else
begin
print ltrim(str(@id))+','+@name+','+ltrim(str(@gender))
fetch next from cur into @id,@name,@gender
print '睡觉了'
end
end
close cur
deallocate cur
/*
1001,tracy,1
hello
1002,lily,2
hello
1003,kobe,1
nihao
1004,lucy,2
睡觉了
1005,nash,1
睡觉了
*/
看我的代码
郁闷啊~~~~
begin
print ltrim(str(@id))+','+@name+','+ltrim(str(@gender))
fetch next from cur into @id,@name,@gender
print '睡觉了'
end那我加了怎么没出错?
GO
/****** Object: StoredProcedure [dbo].[sp_count_dpt] Script Date: 05/15/2012 14:09:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER procedure [dbo].[sp_count_dpt]
@dptname nvarchar(50),
@dptdate1 nvarchar(50),
@dptdate2 nvarchar(50)
as declare
@dpttemp nvarchar(50),
@dpt1temp nvarchar(50),
@tempct int
delete from dptcount
declare cur cursor for( select dpt,dpt1 from dpt1 where dpt=@dptname)--用游标取得所有机构关键词
open cur
fetch next from cur into @dpttemp,@dpt1temp
while @@FETCH_STATUS=0
begin
if @dptname='全部机构'
begin
set @tempct =
(select COUNT(*) from thread where threadtitle like '%'+@dpt1temp+'%' and
threaddate>=CAST(@dptdate1 as datetime) and threaddate<=CAST(@dptdate2 as datetime))
insert into dptcount(dpt,dpt1,dptcount) values(@dpttemp,@dpt1temp,@tempct)
fetch next from cur into @dpttemp,@dpt1temp
end
else
begin
end
end
close cur
deallocate cur全部代码,是在sqlserver2008 里修改存储过程的代码。
ps:怎么贴出sql code效果的?
消息 156,级别 15,状态 1,过程 sp_count_dpt,第 31 行
关键字 'end' 附近有语法错误。
消息 102,级别 15,状态 1,过程 sp_count_dpt,第 33 行
'cur' 附近有语法错误。
ALTER procedure [dbo].[sp_count_dpt]
@dptname nvarchar(50),
@dptdate1 nvarchar(50),
@dptdate2 nvarchar(50)
as declare
@dpttemp nvarchar(50),
@dpt1temp nvarchar(50),
@tempct int
delete from dptcount
declare cur cursor for( select dpt,dpt1 from dpt1 where dpt=@dptname)--用游标取得所有机构关键词
open cur
fetch next from cur into @dpttemp,@dpt1temp
while @@FETCH_STATUS=0
begin
if @dptname='全部机构'
begin
set @tempct =
(select COUNT(*) from thread where threadtitle like '%'+@dpt1temp+'%' and
threaddate>=CAST(@dptdate1 as datetime) and threaddate<=CAST(@dptdate2 as datetime))
insert into dptcount(dpt,dpt1,dptcount) values(@dpttemp,@dpt1temp,@tempct)
fetch next from cur into @dpttemp,@dpt1temp
end
else
begin
--这里必须写代码
end
end
close cur
deallocate cur
ALTER procedure [dbo].[sp_count_dpt]
@dptname nvarchar(50),
@dptdate1 nvarchar(50),
@dptdate2 nvarchar(50)
as declare
@dpttemp nvarchar(50),
@dpt1temp nvarchar(50),
@tempct int
delete from dptcount
declare cur cursor for( select dpt,dpt1 from dpt1 where dpt=@dptname)--用游标取得所有机构关键词
open cur
fetch next from cur into @dpttemp,@dpt1temp
while @@FETCH_STATUS=0
begin
if @dptname='全部机构'
begin
set @tempct =
(select COUNT(*) from thread where threadtitle like '%'+@dpt1temp+'%' and
threaddate>=CAST(@dptdate1 as datetime) and threaddate<=CAST(@dptdate2 as datetime))
insert into dptcount(dpt,dpt1,dptcount) values(@dpttemp,@dpt1temp,@tempct)
fetch next from cur into @dpttemp,@dpt1temp
end
else
begin
--这里必须写代码
end
end
close cur
deallocate cur