使用不工作的声明变量从函数创建TSQL视图

Question

我可以使用下面的代码轻松地从函数创建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

但是，为什么我不能为函数创建一个带有声明变量的视图呢？下面的代码导致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

Answer 1

您不能在视图中声明变量；CREATE VIEW只是SELECT (或以CTE开头的SELECT )的包装器，不能被参数化。试图用视图中的值对这些变量进行硬编码，似乎一开始就违背了使用变量的目的:为了更改这些值，您需要修改视图。

接下来，你似乎想说：

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;