在执行计划里会提示
对象名 '#tt' 无效。 不知为什么.create table dbo.base_flow(pro_no varchar(30),ver varchar(10),itemno smallint,itemorder numeric(5,2))
go
insert into dbo.base_flow
select 'aa' ,'a', 101,1 union all
select 'aa' ,'a', 102,2 union all
select 'aa' ,'a', 103,3 union all
select 'aa' ,'a', 101,4 union all
select 'bb' ,'a', 102,1 union all
select 'bb' ,'a', 105,2 union all
select 'bb' ,'a', 106,3 union all
select 'bb' ,'a', 103,4
go
create
--alter
procedure dbo.flow_available
@pro_no varchar(30),
@ver varchar(10)
as
set nocount on
declare @minorder numeric(5,2) ,
@maxorder numeric(5,2) ,
@itemno smallint create table #tt(itemno smallint ,id int ,id2 varchar(11)) insert into #tt(itemno)
select itemno
from dbo.base_flow
where pro_no = @pro_no and ver = @ver
group by itemno
having count(1)>1
declare cur cursor for
select itemno
from #tt
open cur fetch next from cur into
@itemno
while (@@fetch_status = 0)
begin
select @maxorder = max(itemorder) ,@minorder = min(itemorder)
from dbo.base_flow
where pro_no = @pro_no and ver = @ver
and itemno = @itemno
group by itemno
if exists (
select 1 from dbo.base_flow
where pro_no = @pro_no and ver = @ver
and itemno <> @itemno
and itemorder between @maxorder and @minorder )
begin
select '出错...........'
drop table #tt
return
end
end
fetch next from cur into @itemno close cur
deallocate cur
drop table #tt
set nocount offGO -- EXEC dbo.flow_available 'aa', 'A'
对象名 '#tt' 无效。 不知为什么.create table dbo.base_flow(pro_no varchar(30),ver varchar(10),itemno smallint,itemorder numeric(5,2))
go
insert into dbo.base_flow
select 'aa' ,'a', 101,1 union all
select 'aa' ,'a', 102,2 union all
select 'aa' ,'a', 103,3 union all
select 'aa' ,'a', 101,4 union all
select 'bb' ,'a', 102,1 union all
select 'bb' ,'a', 105,2 union all
select 'bb' ,'a', 106,3 union all
select 'bb' ,'a', 103,4
go
create
--alter
procedure dbo.flow_available
@pro_no varchar(30),
@ver varchar(10)
as
set nocount on
declare @minorder numeric(5,2) ,
@maxorder numeric(5,2) ,
@itemno smallint create table #tt(itemno smallint ,id int ,id2 varchar(11)) insert into #tt(itemno)
select itemno
from dbo.base_flow
where pro_no = @pro_no and ver = @ver
group by itemno
having count(1)>1
declare cur cursor for
select itemno
from #tt
open cur fetch next from cur into
@itemno
while (@@fetch_status = 0)
begin
select @maxorder = max(itemorder) ,@minorder = min(itemorder)
from dbo.base_flow
where pro_no = @pro_no and ver = @ver
and itemno = @itemno
group by itemno
if exists (
select 1 from dbo.base_flow
where pro_no = @pro_no and ver = @ver
and itemno <> @itemno
and itemorder between @maxorder and @minorder )
begin
select '出错...........'
drop table #tt
return
end
end
fetch next from cur into @itemno close cur
deallocate cur
drop table #tt
set nocount offGO -- EXEC dbo.flow_available 'aa', 'A'
解决方案 »
- sql怎样截取某个符号第一次出现位置的前面的字符?
- 请教这样的SQL如何写?
- 问个问题 实在想不明白 关于All和Any的
- 考察日期函数datediff 和 dateadd 以及 getdate() 函数
- 一个高难度问题(行列转化汇总)
- 请问如何添加图像型数据进数据库
- 要访问jsp的网站,必须要打端口号吗?比如,我的ip是61.13.123.123,要访问jsp网页,就要打http://61.13.123.123:8080/index.jsp吗?谢!
- 一个触发器的问题!急!
- 请帮帮我:怎样在LIST控件中显示SQL SERVER的表中的数据。。怎样用SQL SERVER让用户创建自己的数据库。
- 请问那位大虾,visual foxpro 做的数据库 可否实现多人同时访问? 如何实现? 谢谢
- 存储过程执行结果有时不正确但不提示错误(查询|游标|临时表|存储过程分页)
- cai
2.
...
return
end
fetch next from cur into @itemno
end