Create TSQL view from function with DECLARE variables not working

April 4, 2022

I can easily create a SQL view from a function using the code below.

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

HOWEVER, why can I not create a View with DECLARED variables for the function??
The below code causes TSQL to have several error messages thrown.

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

>Solution :

You can’t declare variables in a view; CREATE VIEW is just a wrapper for a SELECT (or a SELECT that starts with a CTE), and can’t be parameterized. Trying to hard-code these variables with values inside the view seems to defeat the purpose of using variables in the first place: in order to change the values, you need to alter the view.

Next, you seem to be trying to say:

SELECT * FROM <stored_procedure>;

You can’t interact with a stored procedure this way. If this is in fact a table-valued function with a bad name, you should rename it, because on top of the fact that giving a function a prefix of sp_ is misleading, you shouldn’t be using the sp_ prefix on anything. (Background.)

You can get close to what you’re looking for like this:

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;