存储过程错误,执行存储过程数据没有错,但是报上面的错误.CREATE procedure ChangeOrgan
(
@up_organ int,--上级机构
@my_organ int--本级机构
)
as
--declare @up_organ int
--set @up_organ=7
--declare @my_organ int
--set @my_organ=54DECLARE @ERROR INT
SELECT @ERROR=0declare @my_treeno varchar(20)--取本级机构树NO
declare @up_treeno varchar(20)--取要移动到的父机构树NO
declare @max_treeno varchar(20)
declare @Channel_type_id int----上级通道类型
declare @my_channel_type_id int --本级通道类型
select @Channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@up_organ
select @my_channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@my_organ
select @my_treeno=sys_tree_no from sys_organ where organ_sys_id=@my_organ
select @up_treeno=sys_tree_no from sys_organ where organ_sys_id=@up_organ
------要移动的机构的下面的所有机构ID
--drop table #tmp
select organ_sys_id into #tmp from sys_organ where sys_tree_no like @my_treeno+'%'
----------本级机构不为1(总部)---------------------------------------------------------------
BEGIN TRAN
if @my_organ<>1
begin
if len(@my_treeno)>len(@up_treeno)-1
begin
---可以随便移动-----
---查询父机构下的同级最大树编号declare @max_treeno varchar(20)
select @max_treeno=max(sys_tree_no) from sys_organ where up_organ_sys_id=@up_organ
set @max_treeno='0'+cast(@max_treeno+1 as varchar)
--修改树NO
update sys_organ
set sys_tree_no=@max_treeno+substring(sys_tree_no,len(@my_treeno)+1,len(sys_tree_no))
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
--本机构的上级机构
update sys_organ
set up_organ_sys_id=@up_organ
where organ_sys_id=@my_organ
SELECT @ERROR=@ERROR+@@ERROR
--------------------------------------------------------
update sys_organ
set org_level_recno=len(sys_tree_no)/2,
Channel_type_id=@Channel_type_id+Channel_type_id-@my_channel_type_id+1
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
-------------------------------------------------------------------
insert into tmp_sys_organ_update(organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId)
select organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId
from sys_organ where organ_sys_id in (select * from #tmp)
-----------------------------------------------------------------------------
SELECT @ERROR=@ERROR+@@ERROR
drop table #tmp
SELECT @ERROR=@ERROR+@@ERROR
end
else
begin
---不能移动到它的下级菜单中----------
if @my_treeno<>substring(@up_treeno,1,len(@my_treeno))
begin
----移动位置------
select @max_treeno=max(sys_tree_no) from sys_organ where up_organ_sys_id=@up_organ
set @max_treeno='0'+cast(@max_treeno+1 as varchar)
--print @max_treeno
update sys_organ
set sys_tree_no=@max_treeno+substring(sys_tree_no,len(@my_treeno)+1,len(sys_tree_no))
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
update sys_organ
set up_organ_sys_id=@up_organ
where organ_sys_id=@my_organ
SELECT @ERROR=@ERROR+@@ERROR
update sys_organ
set org_level_recno=len(sys_tree_no)/2,
Channel_type_id=@Channel_type_id+Channel_type_id-@my_channel_type_id+1
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
-------------------------------------------------------------------
insert into tmp_sys_organ_update(organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId)
select organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId
from sys_organ where organ_sys_id in (select * from #tmp)
-----------------------------------------------------------------------------
SELECT @ERROR=@ERROR+@@ERROR
drop table #tmp
SELECT @ERROR=@ERROR+@@ERROR
end
end
end
IF (@ERROR=0)
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO
(
@up_organ int,--上级机构
@my_organ int--本级机构
)
as
--declare @up_organ int
--set @up_organ=7
--declare @my_organ int
--set @my_organ=54DECLARE @ERROR INT
SELECT @ERROR=0declare @my_treeno varchar(20)--取本级机构树NO
declare @up_treeno varchar(20)--取要移动到的父机构树NO
declare @max_treeno varchar(20)
declare @Channel_type_id int----上级通道类型
declare @my_channel_type_id int --本级通道类型
select @Channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@up_organ
select @my_channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@my_organ
select @my_treeno=sys_tree_no from sys_organ where organ_sys_id=@my_organ
select @up_treeno=sys_tree_no from sys_organ where organ_sys_id=@up_organ
------要移动的机构的下面的所有机构ID
--drop table #tmp
select organ_sys_id into #tmp from sys_organ where sys_tree_no like @my_treeno+'%'
----------本级机构不为1(总部)---------------------------------------------------------------
BEGIN TRAN
if @my_organ<>1
begin
if len(@my_treeno)>len(@up_treeno)-1
begin
---可以随便移动-----
---查询父机构下的同级最大树编号declare @max_treeno varchar(20)
select @max_treeno=max(sys_tree_no) from sys_organ where up_organ_sys_id=@up_organ
set @max_treeno='0'+cast(@max_treeno+1 as varchar)
--修改树NO
update sys_organ
set sys_tree_no=@max_treeno+substring(sys_tree_no,len(@my_treeno)+1,len(sys_tree_no))
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
--本机构的上级机构
update sys_organ
set up_organ_sys_id=@up_organ
where organ_sys_id=@my_organ
SELECT @ERROR=@ERROR+@@ERROR
--------------------------------------------------------
update sys_organ
set org_level_recno=len(sys_tree_no)/2,
Channel_type_id=@Channel_type_id+Channel_type_id-@my_channel_type_id+1
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
-------------------------------------------------------------------
insert into tmp_sys_organ_update(organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId)
select organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId
from sys_organ where organ_sys_id in (select * from #tmp)
-----------------------------------------------------------------------------
SELECT @ERROR=@ERROR+@@ERROR
drop table #tmp
SELECT @ERROR=@ERROR+@@ERROR
end
else
begin
---不能移动到它的下级菜单中----------
if @my_treeno<>substring(@up_treeno,1,len(@my_treeno))
begin
----移动位置------
select @max_treeno=max(sys_tree_no) from sys_organ where up_organ_sys_id=@up_organ
set @max_treeno='0'+cast(@max_treeno+1 as varchar)
--print @max_treeno
update sys_organ
set sys_tree_no=@max_treeno+substring(sys_tree_no,len(@my_treeno)+1,len(sys_tree_no))
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
update sys_organ
set up_organ_sys_id=@up_organ
where organ_sys_id=@my_organ
SELECT @ERROR=@ERROR+@@ERROR
update sys_organ
set org_level_recno=len(sys_tree_no)/2,
Channel_type_id=@Channel_type_id+Channel_type_id-@my_channel_type_id+1
where organ_sys_id in (select * from #tmp)
SELECT @ERROR=@ERROR+@@ERROR
-------------------------------------------------------------------
insert into tmp_sys_organ_update(organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId)
select organ_sys_id,sys_tree_no,organ_man_id,organ_name,org_level_recno,up_organ_sys_id,
country,organ_city,addr,postalcode,tele,fax,rele_man,website,email,organ_des,organ_note,
rec_created,Channel_type_id,create_date,running_date,liquidate_date,stop_date,organ_state_id,
organ_sName,own_db,FIsConfirm,FRequestOnlyTop,FCityId
from sys_organ where organ_sys_id in (select * from #tmp)
-----------------------------------------------------------------------------
SELECT @ERROR=@ERROR+@@ERROR
drop table #tmp
SELECT @ERROR=@ERROR+@@ERROR
end
end
end
IF (@ERROR=0)
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO
select @my_channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@my_organ
select @my_treeno=sys_tree_no from sys_organ where organ_sys_id=@my_organ
select @up_treeno=sys_tree_no from sys_organ where organ_sys_id=@up_organ
-------------------
返回多个值。
一个变量只能赋一个值。
select @Channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@up_organ
select @my_channel_type_id=Channel_type_id from sys_organ where organ_sys_id=@my_organ
select @my_treeno=sys_tree_no from sys_organ where organ_sys_id=@my_organ
select @up_treeno=sys_tree_no from sys_organ where organ_sys_id=@up_organ
-------------------
返回多个值。
一个变量只能赋一个值。
-------------------------------------------------
理解有誤,這段代碼是不會出錯的,會把最後一個sys_tree_no賦值給@up_treeno。看看這個例子。
Declare @T Table (ID Int, TESTTime smalldatetime)
Insert @T Select 1,'2006-6-20'
Union All Select 2,'2005-1-16'Declare @ID Int
Select @ID=ID From @T
Select @ID
这个就错了。
Declare @T Table (ID Int,dd int, TESTTime smalldatetime)
Insert @T Select 1,1,'2006-6-20'
Union All Select 2,1,'2005-1-16'
Union All Select 2,2,'2005-1-26'Declare @ID Int
Set @ID=(select dd From @T where id=2)
Select @ID
(所影响的行数为 3 行)服务器: 消息 512,级别 16,状态 1,行 7
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。(所影响的行数为 1 行)
你是不是有触发器啊
----------------------
聰明!!沒想到這個上,照這個分析,很有可能是某個表上的觸發器有問題。
一律改为
where organ_sys_id in (select organ_sys_id from #tmp)