我正在使用INNER JOIN创建一个存储过程来连接SQL中的7个表,但是我得到了一个关于错误语法的错误。
我试着调整格式,但我不能得到正确的语法。有人知道我哪里出错了吗?我收到一个错误,说明
"WHERE“附近的语法不正确
我的代码:
CREATE PROCEDURE [dbo].[spInvoicing]
@InvoiceId INT
AS
BEGIN
SELECT
tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblClients.Firstname, tblClients.Surname, tblClients.Address, tblClients.City,
tblClients.Postcode, tblClients.Email, tblClients.Phone,
tblAppointments.AppointmentId, tblAppointments.DateOfAppointment,
tblProductsUsed.ProductsUsedId, tblProducts.ProductName, tblProductsUsed.Quantity,
tblPointofSale.SaleId, tblPointOfSale.CostOfProducts, tblPointOfSale.CostOfServices,
tblPointOfSale.VAT, tblInvoices.SaleAmount, tblInvoices.PaymentMethod
FROM
tblClients
INNER JOIN
tblAppointments ON tblClients.ClientId = tblAppointments.ClientId
INNER JOIN
tblPointOfSale
INNER JOIN
tblInvoices ON tblPointOfSale.SaleId = tblInvoices.SaleId
INNER JOIN
tblProductsUsed
INNER JOIN
tblProducts ON tblProductsUsed.ProductId = tblProducts.ProductId
INNER JOIN
tblServicesRendered
INNER JOIN
tblServices ON tblServicesRendered.ServiceId = tblServices.ServiceId ON tblServicesRendered.AppointmentId = tblAppointments.AppointmentId
ON tblPointOfSale.AppointmentId = tblAppointments.AppointmentId
AND tblProductsUsed.SaleId = tblPointOfSale.SaleId
AND tblPointOfSale.ClientId = tblClients.ClientId
WHERE
tblInvoices.InvoiceId = @InvoiceId
END
发布于 2019-04-18 08:44:58
你的连接有点乱了。在下面尝试一下,看看它是否修复了语法错误。
始终尝试让您的连接遵循INNER JOIN [Table2] ON [Table2].[Field1] = [Table1].[Field1]
格式
FROM tblClients
INNER JOIN tblAppointments ON tblClients.ClientId = tblAppointments.ClientId
INNER JOIN tblPointOfSale ON tblPointOfSale.AppointmentId = tblAppointments.AppointmentId
AND tblPointOfSale.ClientId = tblClients.ClientId
INNER JOIN tblInvoices ON tblPointOfSale.SaleId = tblInvoices.SaleId
INNER JOIN tblProductsUsed ON tblProductsUsed.SaleId = tblPointOfSale.SaleId
INNER JOIN tblProducts ON tblProductsUsed.ProductId = tblProducts.ProductId
INNER JOIN tblServicesRendered ON tblServicesRendered.AppointmentId = tblAppointments.AppointmentId
INNER JOIN tblServices ON tblServicesRendered.ServiceId = tblServices.ServiceId
发布于 2019-04-18 09:32:34
我只是将你的代码复制并粘贴到这个网站-- http://poorsql.com/中,它为你突出了这个错误(你在joins中丢失了)。正如@DBro所说-养成将连接放在新行上的习惯,并将其格式稍微改变一下,这样你就会有更少的麻烦。
https://stackoverflow.com/questions/55737664
复制相似问题