关键字选择附近语法错误

Question

创建一个函数，当传入VendorID时，该函数将返回供应商的剩余余额。在查询中使用此函数返回所有CA供应商及其余额

USE AP
GO
CREATE FUNCTION fnBalanceVendor
(@vendorid INT)
Returns Table
RETURN (SELECT Vendors.VendorID, (InvoiceTotal - PaymentTotal - CreditTotal) AS Balance
FROM Vendors Join Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE Vendors.VendorID = @vendorid)


SELECT *
FROM Vendors INNER JOIN dbo.fnBalanceVendor(8) AS ca ON Vendors.VendorID = ca.VendorID
WHERE VendorState = 'CA'

Msg 156, Level 15, State 1, Procedure fnBalanceVendor, Line 9 [Batch Start Line 2]

关键字'SELECT'附近语法错误。

不确定我在这里做错了什么，任何帮助都会很感激，谢谢!

Answer 1

我建议用" go"在CREATE FUNCTION语句的RETURN语句之后关闭批处理，因此:

USE AP
GO
CREATE FUNCTION fnBalanceVendor
(@vendorid INT)
Returns Table
RETURN (SELECT Vendors.VendorID, (InvoiceTotal - PaymentTotal - CreditTotal) AS Balance
FROM Vendors Join Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE Vendors.VendorID = @vendorid)
GO
SELECT *
FROM Vendors INNER JOIN dbo.fnBalanceVendor(8) AS ca ON Vendors.VendorID = ca.VendorID
WHERE VendorState = 'CA'