tSQL动态存储过程源视图列

Question

我正在尝试使用存储过程动态构建View列。只要我的存储过程参数是静态值，这就非常有效，如本例所示：

CREATE VIEW MyTestView AS SELECT T1.ItemId, T1.BatchId, 
CAST ((SELECT * FROM OPENQUERY([LOCALSERVER], 'EXEC [MyDatabase].dbo.MyStoredProcedure
''MyItemId'', ''MyBatchNumber''')) AS NVARCHAR(64)) AS FromStoredProc
FROM MyTable AS T1

但是，我需要存储过程使用查询中另一个表的输入作为参数值，以便为每一行带回有用的数据。

CREATE VIEW MyTestView AS SELECT T1.ItemId, T1.BatchId, 
CAST ((SELECT * FROM OPENQUERY([LOCALSERVER], 'EXEC [MyDatabase].dbo.MyStoredProcedure
T1.ItemId, T1.BatchId')) AS NVARCHAR(64)) AS FromStoredProc
FROM MyTable AS T1

是否有任何方法将T1的值作为参数传递给存储过程？

感谢

Answer 1

试试这个。我想由于T1的模糊性，你无法运行它

CREATE VIEW MyTestView AS SELECT 
T1.ItemId, T1.BatchId, 
CAST ((SELECT * FROM 
OPENQUERY([LOCALSERVER], 'EXEC 
[MyDatabase].dbo.MyStoredProcedure
ItemId, BatchID')) AS NVARCHAR(64)) 
AS FromStoredProc
FROM MyTable AS T1