第一步
create table a
( fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into a
values(1,'1','1')
第二步
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_c' AND type = 'P')
DROP PROCEDURE p_c
GO
CREATE PROCEDURE p_c
AS
SELECT * from a
GO
EXECUTE p_c
GO
第三
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_b' AND type = 'P')
DROP PROCEDURE p_b
GO
CREATE PROCEDURE p_b
AS
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'b' AND type = 'U')
DROP TABLE b
create table b
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into b exec p_c
select * from b
GO
EXECUTE p_b
GO
第四
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_a' AND type = 'P')
DROP PROCEDURE p_a
GO
CREATE PROCEDURE p_a
AS
create table #c
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into #c exec p_b
select * from #c
GO
EXECUTE p_a
GO
问题,在第三步的时候没有问题的,但到了第四步的时候却提示
服务器: 消息 8164,级别 16,状态 1,过程 p_b,行 17
INSERT EXEC 语句不能嵌套。
(所影响的行数为 0 行)
(所影响的行数为 0 行)
各位有什么高见!
Answer:
把第二层改为function
--第一
create table a
(fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into a
values(1,'1','1')
--第二步
CREATE FUNCTION p_c ()
RETURNS @a TABLE
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
AS
BEGIN
INSERT @a
SELECT * FROM a
RETURN
END
--第三
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_b' AND type = 'P')
DROP PROCEDURE p_b
GO
CREATE PROCEDURE p_b
AS
set nocount on
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'b' AND type = 'U')
DROP TABLE b
create table b
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into b SELECT * from p_c ()
select * from b
GO
EXECUTE p_b
--第四
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'p_a' AND type = 'P')
DROP PROCEDURE p_a
GO
CREATE PROCEDURE p_a
AS
set nocount on
create table #c
(
fid numeric(10),
fno varchar(10),
fname varchar(50)
)
insert into #c exec p_b
select * from #c
GO
EXECUTE p_a