如何将结果(包括存储过程中的设置变量)自动保存到表中

Question

--Drop the procedure if exists
drop proc if exists [Sp_TestA]
CREATE PROCEDURE [dbo].[Sp_TestA]
(@P1 AS VARCHAR(50),
@P2 AS VARCHAR(50),
@P3 AS VARCHAR(50),
@P4 AS VARCHAR(50)) 
AS 
BEGIN
SELECT @P1 AS Col1, @P2 AS Col2, @P3 AS Col3, @p4 AS Col4
DECLARE @col5 VARCHAR(50)
SET @col5 = 'test'
SELECT @col5 AS 'test'
END
--To save the results of procedure into a table automatically
Declare @TblRtRcs TABLE
(
Col1 VARCHAR(50) NOT NULL,
Col2 VARCHAR(50) NOT NULL,
Col3 VARCHAR(50) NOT NULL,
Col4 VARCHAR(50) NOT NULL
);
INSERT INTO @TblRtRcs(Col1, Col2, Col3, Col4)
EXEC [dbo].[Sp_TestA] @p1 = '5' , @p2 ='5', @p3 ='51', @p4 = '22'
SELECT * 
FROM @TblRtRcs;

Answer 1

如果存储过程返回两个数据集，则 insert 语句将失败。插入@table 执行SP_... 只允许一个数据集插入到临时表中。 你可以做一个黑客 - 你可以联合 2 数据集，然后插入到临时表中。使用一些自定义列，您可以分隔行。

Answer 2

--Drop the procedure if exists
drop proc if exists [Sp_TestA]
CREATE PROCEDURE [dbo].[Sp_TestA]
(@P1 AS VARCHAR(50)
,@P2 AS VARCHAR(50)
,@P3 AS VARCHAR(50)
,@P4 AS VARCHAR(50)) 
as begin
--Declare the variable type
declare @col5 varchar(50)
set @col5 = 'test'
--Selecting the required columns as output
SELECT @P1 AS Col1,@P2 AS Col2,@P3 AS Col3, @p4 AS Col4,@col5 
END
--Steps to insert the results into procedure
--1. Declare the table variables
Declare @TblRtRcs TABLE
(Col1 VARCHAR(50)NOT NULL
,Col2 VARCHAR(50)NOT NULL
,Col3 VARCHAR(50)NOT NULL
,Col4 VARCHAR(50)NOT NULL
,Col5 VARCHAR(50)NOT NULL);
--2. Insert the values automatically to the table
INSERT INTO @TblRtRcs(Col1,Col2,Col3,Col4,col5)
--3. Execute the procedure
exec [dbo].[Sp_TestA] @p1 = '5' , @p2 ='5', @p3 ='51', @p4 = '22'
--4. Verify the output of the table
SELECT * FROM @TblRtRcs;