INSERT EXEC 语句

INSERT EXEC语句可以把存储过程或动态SQL批处理返结果集插入目标数据表中，下面例子是以存储过程返回结果集插入一个表变量中，

演示例子，首选创建一个存储过程，这个存储过程需要传入一个参数，是否在职的Member。

代码

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

  

创建一个表变量,

DECLARE   @IsJobMember   AS   TABLE
(
[ MemberId ]   [ int ]   NOT   NULL ,
     [ Name ]   [ nvarchar ] ( 100 )  NOT   NULL ,
     [ Job ]   [ bit ]   NULL
);

  

最后是真正使用INSERT EXEC来把存储过程结果插入表变量中，

INSERT   INTO   @IsJobMember   EXECUTE  dbo.usp_Member_Job  1 ;