Solved

Stored Procedure

Posted on 2011-02-13
2
204 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 39
Log Backup 2 21
Haw to apply join on 2 tables with this scenario 4 22
SQL server is using more virtual memory. 5 63
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now