SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Stored Procedure Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 03/11/2013 :  06:40:20  Show Profile  Reply with Quote
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

64 Posts

Posted - 03/11/2013 :  06:43:54  Show Profile  Reply with Quote
Ok solved it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/11/2013 :  06:46:43  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/11/2013 :  06:47:09  Show Profile  Reply with Quote
quote:
Originally posted by deanglen

Ok solved it!


Ok..cool

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000