Stored Procedure – Update Item Costs

***Warning***

This script has code that will update data in the Dynamics GP database.  While this is not “illegal” it may or may not be supported by Microsoft.  I have not had any issues with this in our environment, but make sure you test this in your non production environment first, before deploying it to your production environment.

***End Warning***

The following stored procedure will allow you to pass in current cost, standard cost, and list price for an item and update the item card in GP.

You can name the procedure whatever you want by change the [tspspUpdateItemCosts] below.  Also the USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

USE [TSP]
GO
/****** Object:  StoredProcedure [dbo].[tspspUpdateItemCosts]    Script Date: 03/24/2010 09:34:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Ron Wilson
— Create date: 10/14/2009
— Description:    This procedure will update current cost, standard cost, and list price
— for an @item_number
— =============================================
ALTER PROCEDURE [dbo].[tspspUpdateItemCosts]
    — Add the parameters for the stored procedure here
    @item_number varchar(30),
    @current_cost MONEY,
    @standard_cost MONEY,
    @list_price MONEY
AS
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
        SET NOCOUNT ON ;

    /*Update all customers who’s balance is over the credit amount (OVER LIMIT)*/
        UPDATE  IV00101
        SET     currcost = @current_cost,
                stndcost = @standard_cost
        WHERE   itemnmbr = @item_number

        UPDATE  dbo.IV00105
        SET     LISTPRCE = @list_price
        WHERE   ITEMNMBR = @item_number
    END