Solved

Stored Procedure

Posted on 2011-02-13
2
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Assisted Solution

by:BurnieP
BurnieP earned 250 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 250 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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