INSERT EXEC语句可以把存储过程或动态SQL批处理返结果集插入目标数据表中,下面例子是以存储过程返回结果集插入一个表变量中,
演示例子,首选创建一个存储过程,这个存储过程需要传入一个参数,是否在职的Member。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
IF
OBJECT_ID
(
'
dbo.usp_Member_Job
'
,
'
p
'
)
IS
NOT
NULL
DROP PROCEDURE dbo.usp_Member_Job
GO
CREATE PROCEDURE dbo.usp_Member_Job
@IsJob BIT
AS
IF @IsJob = 1
SELECT [ MemberId ] , [ Name ] , [ Job ] FROM [ dbo ] . [ Member ] WHERE [ Job ] = @IsJob
ELSE
SELECT [ MemberId ] , [ Name ] , [ Job ] FROM [ dbo ] . [ Member ] WHERE [ Job ] = @IsJob OR [ Job ] IS NULL
DROP PROCEDURE dbo.usp_Member_Job
GO
CREATE PROCEDURE dbo.usp_Member_Job
@IsJob BIT
AS
IF @IsJob = 1
SELECT [ MemberId ] , [ Name ] , [ Job ] FROM [ dbo ] . [ Member ] WHERE [ Job ] = @IsJob
ELSE
SELECT [ MemberId ] , [ Name ] , [ Job ] FROM [ dbo ] . [ Member ] WHERE [ Job ] = @IsJob OR [ Job ] IS NULL
创建一个表变量,
DECLARE
@IsJobMember
AS
TABLE
(
[ MemberId ] [ int ] NOT NULL ,
[ Name ] [ nvarchar ] ( 100 ) NOT NULL ,
[ Job ] [ bit ] NULL
);
(
[ MemberId ] [ int ] NOT NULL ,
[ Name ] [ nvarchar ] ( 100 ) NOT NULL ,
[ Job ] [ bit ] NULL
);
最后是真正使用INSERT EXEC来把存储过程结果插入表变量中,
INSERT
INTO
@IsJobMember
EXECUTE
dbo.usp_Member_Job
1
;