Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Stored Procedure Error

Author  Topic 

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-03-11 : 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'.

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-03-11 : 06:43:54
Ok solved it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:46:43
you're missing a comma.
it should be

o.ShippingFirstName, o.ShippingLastName, o.ShippingMethod,

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:47:09
quote:
Originally posted by deanglen

Ok solved it!


Ok..cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -