CODEWITHSUNDEEP
sqlstored-proceduressql-update
Kavitha
Kavitha

Reputation: 1467

Incorrect syntax near '@ItemID'

I am not sure that writing SP with two update statements correct.

I am getting Incorrect syntax near '@ItemID'. Can you suggestion me ... How can i resolve this.

CREATE PROCEDURE [dbo].[ComponentReplaceUSP](@ReplaceItem nvarchar(256) ,@Quantity decimal(28,12) ,@UOM nvarchar(30)) 

AS  
 DECLARE @ItemID INT    
BEGIN       

   Select @ItemID = ItemID  FROM [dbo].[Item] WHERE ItemName =  @ReplaceItem

    UPDATE Item    
      SET Item.ItemName = @ReplaceItem,
      Item.UOM = @UOM
      Where Item.ItemID =@ItemID    


      Update ItemBillOfMaterial
      Set ItemBillOfMaterial.UOM =@UOM,
          ItemBillOfMaterial.Quantity =@Quantity
          Where  ItemBillOfMaterial.CompItem= @ItemID

Upvotes: 0

Views: 200

Answers (3)

Curtis
Curtis

Reputation: 103348

Your CREATE PROCEDURE query requires an END:

CREATE PROCEDURE [dbo].[ComponentReplaceUSP](@ReplaceItem nvarchar(256) ,@Quantity decimal(28,12) ,@UOM nvarchar(30)) 
AS   
DECLARE @ItemID INT  
BEGIN           
   Select @ItemID = ItemID  FROM [dbo].[Item] WHERE ItemName =  @ReplaceItem
END

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

It will complain about whatever the last part of the last query is, because you've forgotten to END the block you BEGIN at line 5.

The accepted answer is wrong with its main assertion that is has anything to do with the DECLARE statement. The following is fine:

create procedure ABC
as
declare @ID int
begin
    select @ID = object_id from sys.objects
end

Upvotes: 0

David Martin
David Martin

Reputation: 12248

It should be just a case of moving the declare below the begin as this needs to be declared within the SP.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

Upvotes: 0

Related Questions