• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Incorrect Syntax Near Declare

Good Day:

I am using SQL Server 2000.  I am trying to write a stored procedure that updates the ExtendedPrice column based on calculations, but I get an error that reads: Incorrect syntax at declare.  My stored procedure is as follows:

CREATE PROCEDURE spUpdQuoteExtendedPriceC
       @QuoteNumber       [nvarchar](8)
       DECLARE @ExtendedPrice Money
AS UPDATE QuoteImport
  SET  @ExtendedPrice =  Quantity * UnitPrice
  SET  @ExtendedPrice=@ExtendedPrice / 100
  SET  ExtendedPrice=@ExtendedPrice
         WHERE
      ( QuoteNumber = @QuoteNumber AND UOM='C')
GO

Any Suggestions?
Thank You,
Denise
0
DeniseGoodheart
Asked:
DeniseGoodheart
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the declare is AFTER the AS

CREATE PROCEDURE spUpdQuoteExtendedPriceC
       @QuoteNumber       [nvarchar](8)
AS 
DECLARE @ExtendedPrice Money 
UPDATE QuoteImport 
  SET  @ExtendedPrice =  Quantity * UnitPrice 
  SET  @ExtendedPrice=@ExtendedPrice / 100
  SET  ExtendedPrice=@ExtendedPrice
         WHERE 
      ( QuoteNumber = @QuoteNumber AND UOM='C')
GO

Open in new window

0
 
ErnariashCommented:

If you need the ExtendedPrice  as parameter see code: thanks
CREATE PROCEDURE spUpdQuoteExtendedPriceC 
@QuoteNumber [nvarchar](8), 
@ExtendedPrice Money 
AS 
  
UPDATE QuoteImport 
SET ExtendedPrice =Quantity * UnitPrice 
SET ExtendedPrice=@ExtendedPrice / 100 
SET ExtendedPrice=@ExtendedPrice 
WHERE 
( QuoteNumber = @QuoteNumber AND UOM='C') 
GO

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
more errors... simplify your code like this:
CREATE PROCEDURE spUpdQuoteExtendedPriceC
 @QuoteNumber       [nvarchar](8)
AS
UPDATE QuoteImport 
   SET ExtendedPrice =  (Quantity * UnitPrice ) / 100
 WHERE QuoteNumber = @QuoteNumber 
   AND UOM='C'
GO

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ErnariashCommented:
Whe do you need @ExtendedPrice Money  as a variable or parameter?
CREATE PROCEDURE spUpdQuoteExtendedPriceC
       @QuoteNumber       [nvarchar](8)
AS
UPDATE QuoteImport 
	  SET  ExtendedPrice=(Quantity * UnitPrice) / 100
WHERE   ( QuoteNumber = @QuoteNumber AND UOM='C')

Open in new window

0
 
ErnariashCommented:

If you really need @ExtendedPrice as variable or parameter this could be the syntax. You only can have one SET with the Update:
Here you have some different  samples how to set variables :)

CREATE PROCEDURE spUpdQuoteExtendedPriceC
       @QuoteNumber   [nvarchar](8)
AS 
  DECLARE @ExtendedPrice Money 
  
----You will get the Price and Quantity / UnitPrice  from dif tables&
  SELECT TOP 1 @ExtendedPrice =  Quantity * UnitPrice  FROM PriceTable  WHERE QuoteNumber =@QuoteNumber
  
  SET  @ExtendedPrice=@ExtendedPrice / 100
    
  UPDATE QuoteImport 
  SET  ExtendedPrice=@ExtendedPrice
  WHERE  ( QuoteNumber = @QuoteNumber AND UOM='C')

Open in new window

0
 
mironCommented:
almost a copy of  Ernariash and Angel code

-- cheers
CREATE PROCEDURE spUpdQuoteExtendedPriceC
       @QuoteNumber nvarchar(8)
AS
UPDATE QuoteImport 
    SET  ExtendedPrice=(Quantity * UnitPrice) / CAST( 100 as money )
WHERE   ( QuoteNumber = @QuoteNumber AND UOM='C')

Open in new window

0
 
DeniseGoodheartAuthor Commented:
All three of you gave great examples.

Many thanks!

Cheers.
Denise

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now