使用存储过程结果查看加入 [英] View joining with stored Procedure results

问题描述

我在应用程序使用的 SQL 服务器中有一个现有视图.我需要加入从存储过程返回的表.存储过程做了很多事情，比如在返回结果之前插入到多个 #temp 表.

I have a existing view in SQL server used by the application. I need to join with table returned from the stored procedure. The stored procedure does lot of things like inserting to multiple #temp tables before returning the result.

我尝试将存储过程转换为表值函数.但是插入到 TVF 中的临时表会导致编译错误.

I tried to convert the stored procedure to Table valued function. But inserting to temp table inside the TVF causes compilation error.

有没有其他方法可以实现这一点.

Is there any other way I can achieve this.

谢谢

推荐答案

您可以将存储过程的结果插入到临时表中，然后将其加入到视图中.

You can insert the results from the Stored Procedure into a temp table, and then join that onto the view.

看看下面的例子

CREATE TABLE TADA(
  ID INT
);
INSERT INTO TADA VALUES (1),(2);

CREATE VIEW vw_TADA
AS
SELECT *
FROM TADA
WHERE ID <= 1;

CREATE PROCEDURE sp_TADA
AS
SELECT *
FROM TADA
WHERE ID > 1;

CREATE TABLE #TADA(
  ID INT
)
INSERT INTO #TADA EXEC sp_TADA

SELECT *
FROM vw_TADA
UNION ALL
SELECT *
FROM #TADA

这篇关于使用存储过程结果查看加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！