declare @vid int declare @sql char(200) set @vid = (select 编号 from deleted) declare 游标 cursor for select * from getChildOrg(@vid)其中getChildOrg()为函数,我发现这样传参数函数不能运行,为什么呀,应该怎么传参数呀
下面是完整代码:ALTER TRIGGER [dbo].[del] ON [dbo].[人员管理] AFTER delete AS BEGINdeclare @vid int declare @sql char(200) set @vid = (select 编号 from deleted) declare 游标 cursor for select * from getChildOrg(@vid) --set @sql='declare 游标 cursor for select * from getChildOrg'+(@vid) --exec(@sql) OPEN 游标 FETCH NEXT FROM 游标 insert into 测试 (test1) values (@@fetch_status) insert into 测试 (test1) values (@vid) insert into 测试 (test1) values (1) WHILE @@FETCH_STATUS=0 BEGIN insert into 测试 (test1) values (@vid) FETCH NEXT FROM 游标 into @vid END CLOSE 游标 DEALLOCATE 游标 end想做一个删除人员后该人员的直接或间接下属也被删除,因为一直不行所以用了测试表测试结果结果
declare 游标 cursor for select * from getChildOrg(@vid) 这个语句如果用@vid做参数,@@fetch_status总是-1,改成对应数字后就成0了
所以我觉得是参数@VID传的方式不正确
--木有问题create function getChildOrg(@num int) returns @tb table(name varchar(20)) as begin insert into @tb select rtrim(number) from master..spt_values where type='p' and number<@num return end declare @vid int=3select * from getChildOrg(@vid)declare 游标 cursor for select * from getChildOrg(@vid) /* name -------------------- 0 1 2(3 行受影响)
ssp2009: create function getChild(@num int) returns @tb table(name varchar(20)) as begin insert into @tb select rtrim(number) from master..spt_values where type='p' and number <@num return end go declare @vid int set @vid =3 select * from getChild(@vid) declare 游标 cursor for select * from getChild(@vid) select @@fetch_status 0 1 2-1
create function getChildOrg(@num int) returns @tb table(name varchar(20)) as begin insert into @tb select rtrim(number) from master..spt_values where type='p' and number<@num return end declare @vid int=3select * from getChildOrg(@vid)declare 游标 cursor for select * from getChildOrg(@vid)select @@fetch_status DEALLOCATE 游标
/* name -------------------- 0 1 2(3 行受影响) ----------- 0(1 行受影响)
ON [dbo].[人员管理]
AFTER delete
AS
BEGINdeclare @vid int
declare @sql char(200)
set @vid = (select 编号 from deleted)
declare 游标 cursor for select * from getChildOrg(@vid)
--set @sql='declare 游标 cursor for select * from getChildOrg'+(@vid)
--exec(@sql)
OPEN 游标
FETCH NEXT FROM 游标
insert into 测试 (test1) values (@@fetch_status)
insert into 测试 (test1) values (@vid)
insert into 测试 (test1) values (1)
WHILE @@FETCH_STATUS=0
BEGIN
insert into 测试 (test1) values (@vid)
FETCH NEXT FROM 游标 into @vid
END
CLOSE 游标
DEALLOCATE 游标
end想做一个删除人员后该人员的直接或间接下属也被删除,因为一直不行所以用了测试表测试结果结果
--木有问题create function getChildOrg(@num int)
returns @tb table(name varchar(20))
as
begin
insert into @tb
select rtrim(number)
from master..spt_values where type='p' and number<@num
return
end declare @vid int=3select * from getChildOrg(@vid)declare 游标 cursor for select * from getChildOrg(@vid)
/*
name
--------------------
0
1
2(3 行受影响)
returns @tb table(name varchar(20))
as
begin
insert into @tb
select rtrim(number)
from master..spt_values where type='p' and number <@num
return
end
go
declare @vid int
set @vid =3
select * from getChild(@vid)
declare 游标 cursor for select * from getChild(@vid)
select @@fetch_status
0
1
2-1
create function getChildOrg(@num int)
returns @tb table(name varchar(20))
as
begin
insert into @tb
select rtrim(number)
from master..spt_values where type='p' and number<@num
return
end declare @vid int=3select * from getChildOrg(@vid)declare 游标 cursor for select * from getChildOrg(@vid)select @@fetch_status
DEALLOCATE 游标
/*
name
--------------------
0
1
2(3 行受影响)
-----------
0(1 行受影响)
@@fetch_status是个全局游标变量,你刚才没有用fetch next所以你那个0是以前的游标而不是此次的游标