Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure

Posted on 2011-02-13
2
Medium Priority
?
211 Views
Last Modified: 2012-05-11
I need to update this Stored Procedure by adding a row. Please Advise

 I have another table called the Item's Table with Item_ID, Item_description, Item_Price.  I need to create a stored procedure that will add additional item row?  I attempted to creat the procedure and received number error messages

Here is what I created:   When I execute I receive error message: Msg 102, Level 15, State 1, Procedure ADD_ITEM, Line 4 Incorrect syntax near 'NUMERIC'.  

What am  I doing wrong?   Thanks


CREATE PROCEDURE ADD_ITEM  -- Create a new item      
   @item_id_arg NUMERIC,         -- This parameter is the new item ID.
   @item_description_arg VARCHAR(30), -- This parameter is the item description.
   @item_price NUMERIC  -- This parameter is the item price.
   
   AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
  -- Insert the new customer with the parameters given, and a 0 balance.
  INSERT INTO item (item_ID,item_description, item_price)
  VALUES(@item_id_arg,@item_description_arg,@item_price_arg,0);
END;

I changed the arugment '@item_price NUMERIC' to '@item_price_arg NUMERIC' and remove the 0 on the INSERT statement.

I am still receiving INcorrect syntax error.
0
Comment
Question by:alpha100
2 Comments
 
LVL 16

Assisted Solution

by:BurnieP
BurnieP earned 1000 total points
ID: 34882926
Hi,

I have made some modifications to your code.  See if you get better results.  I changed numeric to int and removed the ,0 in the VALUES.
CREATE PROCEDURE ADD_ITEM  -- Create a new item      
   @item_id_arg int,         -- This parameter is the new item ID.
   @item_description_arg VARCHAR(30), -- This parameter is the item description.
   @item_price decimal(18,2)  -- This parameter is the item price.
   
   AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
  -- Insert the new customer with the parameters given, and a 0 balance.
  INSERT INTO item (item_ID,item_description, item_price)
  VALUES(@item_id_arg,@item_description_arg,@item_price_arg);
END;

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 34883878
Try it this way:

CREATE PROCEDURE ADD_ITEM  -- Create a new item      
   @item_id_arg NUMERIC,         -- This parameter is the new item ID.
   @item_description_arg VARCHAR(30), -- This parameter is the item description.
   @item_price NUMERIC  -- This parameter is the item price.
   
   AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
  -- Insert the new customer with the parameters given, and a 0 balance.
  INSERT INTO item (item_ID,item_description, item_price)
  VALUES(@item_id_arg,@item_description_arg,@item_price);
END;

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question