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;