|
deanglen
Yak Posting Veteran
65 Posts |
 Posted - 2013-03-11 : 06:40:20
|
| HiI am trying to add another line to a stored procedure to include another field.The Stored Procedure is as followsUSE [database name]GO/****** Object: StoredProcedure [dbo].[new_OrderExport] Script Date: 03/11/2013 10:22:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[new_OrderExport] AS SET NOCOUNT ONBEGIN 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 ENDWhat I am trying to add is o.ShippingMethod to this line so it reads aso.ShippingFirstName, o.ShippingLastName, o.ShippingMethodI get this error when trying to execute. Anybody know the problem?Msg 156, Level 15, State 1, Procedure new_OrderExport, Line 18Incorrect syntax near the keyword 'left'. |
|
Stored Procedure Error