我可以使用下面的代碼輕松地從函式創建 SQL 視圖。
IF EXISTS(SELECT * FROM [sys].[objects] WHERE object_id = object_id('InvoicedItemsView') AND OBJECTPROPERTY(object_id,'IsView')=1)
DROP VIEW [dbo].[InvoicedItemsView]
GO
CREATE VIEW [dbo].[InvoicedItemsView]
AS
SELECT * FROM sp_GetInvoicedItems('201803', '201803', '201803', '201803',NULL,NULL) AS InvoicedItems
GO
/* SELECT all data from the created view */
SELECT *
FROM [dbo].[InvoicedItemsView]
GO
但是，為什么我不能為函式創建一個帶有 DECLARED 變數的視圖？下面的代碼會導致 TSQL 拋出幾條錯誤訊息。
IF EXISTS(SELECT * FROM [sys].[objects] WHERE object_id = object_id('InvoicedItemsView') AND OBJECTPROPERTY(object_id,'IsView')=1)
DROP VIEW [dbo].[InvoicedItemsView]
GO
CREATE VIEW [dbo].[InvoicedItemsView]
AS
DECLARE @P0 AS CHAR (6) = '201803', @P1 AS NVARCHAR (6) = '201803', @P2 AS CHAR (6) = '201803', @P3 AS NVARCHAR (6) = '201803', @P4 AS INT = NULL, @P5 AS NVARCHAR (4000) = NULL;
SELECT * FROM sp_GetInvoicedItems(@P0, @P1, @P2, @P3, NULL,NULL) AS InvoicedItems
GO
/* SELECT all data from the created view */
SELECT *
FROM [dbo].[InvoicedItemsView]
GO
uj5u.com熱心網友回復：
您不能在視圖中宣告變數；CREATE VIEW只是 a SELECT（或SELECT以 CTE 開頭的 a）的包裝，不能引數化。試圖用視圖內的值對這些變數進行硬編碼似乎違背了首先使用變數的目的：為了更改值，您需要更改視圖。
接下來，您似乎想說：
SELECT * FROM <stored_procedure>;
您不能以這種方式與存盤程序進行互動。如果這實際上是一個名字不好的表值函式，你應該重命名它，因為除了給函式一個前綴sp_是誤導之外，你不應該在任何東西sp_上使用前綴。（背景。）
你可以像這樣接近你正在尋找的東西：
CREATE VIEW dbo.InvoicedItemsView
AS
SELECT i.* -- bad practice, see sqlblog.org/selstar
FROM
(
VALUES -- this "kind of" looks like a variable list:
(
CONVERT(char(6), '201803'),
CONVERT(nvarchar(6), N'201803'),
CONVERT(char(6), '201803'),
CONVERT(nvarchar(6), N'201803'),
CONVERT(int, NULL),
CONVERT(nvarchar(4000), NULL)
)
) AS d(P0, P1, P2, P3, P4, P5)
CROSS APPLY
dbo.sp_GetInvoicedItems(P0, P1, P2, P3, P4, P5) AS i;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/455692.html