|
deanglen
Starting Member
40 Posts |
 Posted - 03/11/2013 : 06:40:20
|
Hi
I am trying to add another line to a stored procedure to include another field.
The Stored Procedure is as follows
USE [database name]
GO
/****** Object: StoredProcedure [dbo].[new_OrderExport]
Script Date: 03/11/2013 10:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[new_OrderExport]
AS
SET NOCOUNT ON
BEGIN
SELECT convert(varchar, getdate(), 103) ExtractDate, o.StoreID, o.OrderNumber OrderNumber,
o.CustomerID, o.FirstName, o.LastName, o.Email,
left(cast(o.OrderNotes as varchar(100)),100) OrderNotes,
o.ShippingFirstName, o.ShippingLastName,
left(left(case o.ShippingCompany when '' then '' else o.ShippingCompany+' ' end,80)+o.ShippingAddress1,100) ShippingAddress1,
left(o.ShippingAddress2,100) ShippingAddress2, left(o.ShippingSuite,30) ShippingSuite, left(o.ShippingCity,50) ShippingCity, left(o.ShippingState,50) ShippingState, left(o.ShippingZip,8) ShippingZip, o.ShippingCountry ShippingCountry,o.ShippingPhone,
cast(isnull(a.ExtensionData,'') as nvarchar(200)) ShippingTitle,
o.ShippingMethodID, convert(varchar(10),o.OrderDate,103) as OrderDate, left(os.OrderedProductSKU,40) SKU, os.Quantity, isnull(os.OrderedProductRegularPrice,0) OrderedProductRegularPrice, ISNULL(os.OrderedProductPrice,0) OrderedProductPrice, isnull(os.OrderedProductSalePrice,0) OrderedProductSalePrice ,
isnull(o.LevelHasNoTax,0) LevelHasNoTax, TaxRate, os.IsTaxable, OrderShippingCosts, o.OrderSubtotal SubTotal,
left(upper(o.BillingFirstName + ' ' + o.BillingLastName),80) AS BillingName,
left(left(case o.BillingCompany when '' then '' else o.BillingCompany+' ' end,80)+o.BillingAddress1,100) BillingAddress1,
left(o.BillingAddress2,100) BillingAddress2, left(o.BillingSuite,30) BillingSuite, left(o.BillingCity,50) BillingCity, left(o.BillingState,50) BillingState, left(o.BillingZip,8) BillingZip, o.BillingCountry BillingCountry, o.BillingPhone,
o.CardType, o.CardName, o.CardNumber, o.CardExpirationMonth+'/'+o.CardExpirationYear CardExpiryDate,
o.CardStartDate, o.CardIssueNumber,
o.PaymentMethod, o.LevelID, os.ProductID, os.VariantID,
c.CustomerID, '"'+isnull(cast(c.ExtensionData as nvarchar(200)),'')+'"' ExtensionData,
m.Name Supplier, p.Name ProductName, '"'+isnull(cast(p.ExtensionData as nvarchar(200)),'')+'"' ProdExtensionData
FROM dbo.Orders o with (NOLOCK)
left join orders_ShoppingCart os WITH (NOLOCK) ON os.OrderNumber = o.OrderNumber
left join Customer c WITH (NOLOCK) on c.CustomerID = o.CustomerID
left join Product p with (NOLOCK) on p.ProductID = os.ProductID
left join ProductManufacturer pm with (NOLOCK) on pm.ProductID = os.ProductID
left join Manufacturer m with (NOLOCK) on m.ManufacturerID = pm.ManufacturerID
left join Address a with (NOLOCK) on a.AddressID = c.ShippingAddressID
WHERE o.OrderNumber = os.OrderNumber AND o.ShippedOn IS NULL
AND (o.TransactionState IN ('AUTHORIZED', 'CAPTURED') OR (o.TransactionState = 'PENDING' and o.PaymentMethod='PURCHASEORDER'))
AND IsNew = 1
AND o.StoreID <> 4
ORDER BY ordernumber
END
What I am trying to add is o.ShippingMethod to this line so it reads as
o.ShippingFirstName, o.ShippingLastName, o.ShippingMethod
I get this error when trying to execute. Anybody know the problem?
Msg 156, Level 15, State 1, Procedure new_OrderExport, Line 18
Incorrect syntax near the keyword 'left'. |
|
Stored Procedure Error
