时间戳

首页 > 解决方案 > SQL Server 2014 错误：必须声明标量变量？在视图中调用函数

java - SQL Server 2014 错误：必须声明标量变量？在视图中调用函数

问题描述

我在尝试创建的视图中调用函数时遇到问题。出现的错误是“必须声明标量变量“@ProductID”。“（Products.Price + GST）AS TotalPrice”中的 GST 也以红色下划线作为问题。我错过了什么？

这是我的功能：

CREATE FUNCTION fn_ReturnTaxedPrice
(
	@ProductID int,
	@GST float
) 
	RETURNS float
AS
BEGIN

	DECLARE @Price float
	DECLARE @GSTPrice float
	SET @Price = (SELECT Price FROM Products WHERE ProductID = @ProductID)
	SET @GSTPrice = @Price * (1 - @GST)
	
	RETURN (@GSTPrice)

END

这是我的看法：

CREATE VIEW vw_SalesDetails
AS
SELECT Sales.SaleNo, 
Sales.SaleDate, 
Customer.FirstName, 
Customer.LastName,
Category.Category, 
Products.ProductDescription, 
Type.Type, 
Products.Year, 
Products.Price,
dbo.fn_ReturnTaxedPrice(@ProductID, 0.1) AS GST,
(Products.Price + GST) AS TotalPrice
FROM Category
JOIN Customer ON Category.CategoryID = Customer.CategoryID
JOIN Sales ON Customer.CustomerID = Sales.CustomerID
JOIN SalesProducts ON Sales.SaleNo = SalesProducts.SaleNo
JOIN Products ON Products.ProductID = SalesProducts.ProductID
JOIN ProductType ON Products.ProductID = ProductType.ProductID
JOIN Type ON Type.TypeID = ProductType.TypeID

标签： javasql-serverdatabasefunctionsql-view

解决方案

我希望这有帮助。

CREATE FUNCTION fn_ReturnTaxedPrice
(
    @ProductID int,
    @GST float
) 
    RETURNS money  --this is the correct data type for money
AS
BEGIN

    DECLARE @Price money

    SET @Price = (SELECT Price FROM Products WHERE ProductID = @ProductID)

    RETURN @Price * @GST 

END

现在应该可以了。

CREATE VIEW vw_SalesDetails
AS
SELECT Sales.SaleNo, 
       Sales.SaleDate,
       Customer.FirstName, 
       Customer.LastName,
       Category.Category, 
       Products.ProductDescription, 
       Type.Type, 
       Products.Year, 
       Products.Price,
       dbo.fn_ReturnTaxedPrice(Products.ProductID, 0.1) AS GST, 
       dbo.fn_ReturnTaxedPrice(Products.ProductID, 0.1) + Products.Price AS TotalPrice
FROM Category
       JOIN Customer ON Category.CategoryID = Customer.CategoryID
       JOIN Sales ON Customer.CustomerID = Sales.CustomerID
       JOIN SalesProducts ON Sales.SaleNo = SalesProducts.SaleNo
       JOIN Products ON Products.ProductID = SalesProducts.ProductID
       JOIN ProductType ON Products.ProductID = ProductType.ProductID
       JOIN Type ON Type.TypeID = ProductType.TypeID

推荐阅读