SQL Server：将存储过程输出的输出保存到不同的表中

As a simple example, suppose I have the following stored procedure in my SQL Server database that produces an output:

举个简单的例子,假设我的SQL Server数据库中有以下存储过程,它产生一个输出:

CREATE PROCEDURE MyProc
    (@Id INT, 
     @Var1 VARCHAR(100))
AS
BEGIN
    SELECT
        Id = @Id,
        Var1 = @Var1,
        Var2 = LTRIM(@Id) + ' - ' + @Var1
END

Now, what I would like to do is have the output from this stored procedure inserted into a table with other columns such as:

现在,我想要做的是将此存储过程的输出插入到包含其他列的表中,例如:

DECLARE @NewTable TABLE 
(
     ExtraCols INT, 
     Id INT, 
     Var1 VARCHAR(100), 
     Var2 VARCHAR(MAX)
)

So, I would like to do something along the lines of:

所以,我想做的事情是:

DECLARE @RunVars TABLE (ExtraCols INT, Id INT, Var1 VARCHAR(100))
    INSERT INTO @RunVars (ExtraCols, Id, Var1)
    VALUES (123, 1, 'Test'),
           (456, 2, 'Test 1')

Then, after running each row of @RunVars through the stored procedure, to end up with:

然后,在通过存储过程运行@RunVars的每一行之后,最终得到:

@NewTable:

ExtraCols  Id       Var1        Var2
---------------------------------------------
123         1       'Test'      '1 - Test'
456         2       'Test 1'    '2 - Test 1'

I know I could create a temporary table to get the data from the stored procedure and then do a join myself, but I'm wondering if there's any better way to accomplish this - kind of a join or cross apply to the stored procedure.

我知道我可以创建一个临时表来从存储过程中获取数据,然后自己进行连接,但我想知道是否有更好的方法来实现这一点 - 一种连接或交叉应用于存储过程。

1 个解决方案

#1

If you can't modify the proc, and OPENQUERY is frowned upon, then no, there's no better way.

如果你不能修改proc,并且OPENQUERY不赞成,那么不,没有更好的方法。