Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Computed Column Issue

    Hi,

    I'm having a problem with a computed field in a table. I have a stored procedure that inserts a row into a table and returns the id and the computed value.
    The computed colmn is returned as a decimal (29,6) but for some reason the value is returned with no decimals (confirmed by the Profiler).
    The value is calculated and displayed in the database properly with decimals. Also, Is Persisted is OFF.

    Below is the stored procedure, computed column foruma (both give same result), and the profiler trace.

    Thanks

    Stored Procedure
    ================================================== ==============
    ALTER PROCEDURE [Purchasing].[ntp_PurchaseOrderDetail_Insert]
    (
    @PurchaseOrderDetailId int OUTPUT,
    @PurchaseOrderId int ,
    @OrderQty decimal (11, 4) ,
    @VendorProductId int ,
    @Description nvarchar (255) ,
    @UnitPrice decimal (18, 6) ,
    @PackingQty decimal (14, 4) ,
    @DueDateValue nvarchar (50) ,
    @ModifiedDate datetime ,
    @IsUnitPriceManual bit ,
    @LineTotal decimal (29, 6) OUTPUT
    )
    AS
    INSERT INTO [Purchasing].[PurchaseOrderDetail]
    (
    [PurchaseOrderID]
    ,[OrderQty]
    ,[VendorVendorProductID]
    ,[Description]
    ,[UnitPrice]
    ,[PackingMethod]
    ,[PackingQty]
    ,[DueDateValue]
    ,[ModifiedDate]
    ,[IsUnitPriceManual]
    )
    VALUES
    (
    @PurchaseOrderId
    ,@OrderQty
    ,@VendorVendorProductId
    ,@Description
    ,@UnitPrice
    ,@PackingMethod
    ,@PackingQty
    ,@DueDateValue
    ,@ModifiedDate
    ,@IsUnitPriceManual
    )

    -- Get the identity value
    SET @PurchaseOrderDetailId = SCOPE_IDENTITY()

    -- Select computed columns into output parameters
    SELECT
    @LineTotal = [LineTotal]
    FROM
    [Purchasing].[PurchaseOrderDetail]
    WHERE
    [PurchaseOrderDetailID] = @PurchaseOrderDetailId


    Computed Column Formula
    ================================================== ==============
    isnull(CONVERT(DECIMAL (29,6),[OrderQty]*([UnitPrice]*[PackingQty])),0.000000)
    or
    isnull([OrderQty]*([UnitPrice]*[PackingQty]),0.000000)

    Profiler Trace
    ================================================== ==============
    declare @p1 int
    set @p1=115
    declare @p16 numeric(29,0) <- should be numeric(29,6)
    set @p16=5 <- value should be 5.259200
    exec Purchasing.PurchaseOrderDetail_Insert @PurchaseOrderDetailId=@p1 output,@PurchaseOrderId=68,@OrderQty=4,@VendorProd uctId=28,@Description=N'93678975 - GL-2222',@UnitPrice=0.657400,@PackingMethod=N'Bags (2)',@PackingQty=2.0000,@DueDateValue=NULL,@Modifi edDate=''2008-05-19 15:06:37:610'',@LineTotal=@p16 output
    select @p1, @p16

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Works fine for me. Can you post complete DML & DDL?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •