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
CodePudding user response:
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;