none
error in running stored procedure RRS feed

  • Question

  • Dear Friends,

    I have the following stored procedure:

    ALTER Procedure [INSERT_NewOrder_Web_2008]
    (

    @realemail nvarchar(255),
    @ProductId int,
    @ProductCategoryId int,
    @distributorId int,
    @orderTotal nvarchar(255) ,
    @orderrequestedtime DateTime,
    @quantity int,
    @productname nvarchar(255),
    @producttype nvarchar(50),
    @deliverymethod nvarchar(50),
    @planDuration nvarchar(50),
    @ExpiryDate Datetime,
    @UsersConnectionType nvarchar(50),
    @WebPlanTypeId int,
    @users int,
    @plantype nvarchar(50),
    @NewOrderHistoryId int output,
    @NewOrderId int output,
    @ErrorCode int output
    )
    as
    Begin


    Begin Transaction

    --declare @nu null
    declare @NOId int
    declare @NHOId int
    --Set @nu =  ''

    Insert into Orders2008_s(realemail) values(@realemail)
    SET @ErrorCode = @@ERROR
                        IF @ErrorCode <> 0
                        --(@Errorout = @ErrorCode
                        GOTO ErrorHandler
    set @NewOrderId = (select IDENT_CURRENT('Orders2008_s'))
    set @NOId = @NewOrderId

    Insert into [ordersHistory2008_S] values
    (@NOId,@productId,@productCategoryId,
    @distributorId,@ordertotal,@orderrequestedtime,'',
    @quantity,@productname,@producttype,'','','','','','PENDING','PRE-ACTIVE',@deliverymethod
    ,'')

    SET @ErrorCode = @@ERROR
                        IF @ErrorCode <> 0
                        --(@Errorout = @ErrorCode
                        GOTO ErrorHandler
    set @newOrderHistoryId =  (select IDENT_CURRENT('orderHistory2008_S'))
    set @NHOId = @newOrderHistoryId

    Insert into WebPurchasedPlan2008_S
    values(@NHOId,@WebPlanTypeId,@productId,@ProductCategoryId,
    @planType,'Not Generated Yet',@Users,0,'NO',@planDuration,@ExpiryDate,
    '','','',@UsersConnectionType)



     SET @ErrorCode = @@ERROR
          IF @ErrorCode <> 0
             GOTO ErrorHandler
         
                 

           COMMIT TRANSACTION
           RETURN
                               
           ErrorHandler:
              ROLLBACK TRANSACTION
    end


    When I run this

    exec INSERT_NewOrder_Web_2008

    'test@testmail.com',71,2,22,'99.99','11/10/2009',

    1,'CNS','WEB','DIGITAL','Monthly',

    '12/10/2009','UNLIMITED',3,0,'STARTER'



    i get this error:



    (1 row(s) affected)
    Msg 8114, Level 16, State 5, Procedure INSERT_NewOrder_Web_2008, Line 58
    Error converting data type varchar to numeric.



    I have checked my columns type, there is no mismatch.



    Any idea????



    Thanks,


    Friday, June 27, 2008 1:52 AM

Answers

  • You're very welcome.  As for newOrderHistoryID, are records getting inserted into WebPurchasedPlan2008_S?  Perhaps it's hitting your GoTo before it runs the Ident_Current query.  Otherwise, it could be a metadata visibility issue (your account not having sufficient rights to properly query ident_current.  Check out the MSDN article on IDENT_CURRENT.  In your case, you may want to use @@IDENTITY OR SCOPE_IDENTITY instead of IDENT_CURRENT.  If another process inserts a record into your table AFTER your insert but BEFORE your IDENT_CURRENT, you won't get the record ID of the row you inserted.

     

    SQL is asking you to provide output variables so it can output what you've requested.  Are you not looking to get that information back from the proc?

    Friday, June 27, 2008 4:35 AM
    Moderator

All replies

  • Can you script out WebPurchasedPlan2008_S and post back with the script?  Line 58 is the insert into WebPurchasedPlan2008_S, so perhaps you have a data type mismatch there.

     

    Are there any triggers on WebPurchasedPlan2008_S?

    Friday, June 27, 2008 2:02 AM
    Moderator
  • YOUR REPLY IS MUCH APPRECIATED:

    HERE IS SCRIPT:

    CREATE TABLE [WebPurchasedPlan2008_S](
        [WebPurchasedPlanId] [int] IDENTITY(1,1) NOT NULL,
        [orderhistoryId] [int] NULL,
        [WebPlanTypeId] [int] NULL,
        [productid] [int] NULL,
        [ProductCategoryId] [int] NULL,
        [PlanType] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ProductCode] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [users] [int] NULL,
        [noofusersactivated] [int] NULL,
        [doesallactivated] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PlanDuration] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ExpiryDate] [datetime] NULL,
        [ActivationDate] [datetime] NULL,
        [DeActivation] [datetime] NULL,
        [insertiondate] [datetime] NULL,
        [UsersConnectionType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_WebPurchasedPlan2008_S] PRIMARY KEY CLUSTERED
    (
        [WebPurchasedPlanId] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [WebPurchasedPlan2008_S]  WITH CHECK ADD  CONSTRAINT [FK_WebPurchasedPlan2008_S_ordersHistory2008_S] FOREIGN KEY([orderhistoryId])
    REFERENCES [ordersHistory2008_S] ([orderhistoryid])
    Friday, June 27, 2008 2:05 AM
  • No problem with that table, but sometimes the line numbers can be a little tricksy.  Can you post the script for

    [ordersHistory2008_S] and Orders2008_s?  I'm thinking it's the former, but it may be the latter.

     

    No triggers on any of these tables, right?

    Friday, June 27, 2008 2:19 AM
    Moderator

  • MANY THANKS,

    YES, NO TRIGGERS, EXCEPT THEY HAVE PK AND FK RELATION:


    CREATE TABLE [orders2008_S](
        [orderid] [int] IDENTITY(1,1) NOT NULL,
        [realemail] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [maskedemail] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [phone] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingaddress1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingaddress2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingcontactname] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [companyname] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingcity] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingregion] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [billingcountry] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingcontactname] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingaddress1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingaddress2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingcity] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingregion] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingpostalcode] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingcountry] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [shippingphone] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_orders2008_S] PRIMARY KEY CLUSTERED
    (
        [orderid] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    --------------------------------------------------------

    CREATE TABLE [ordersHistory2008_S](
        [orderhistoryid] [int] IDENTITY(1,1) NOT NULL,
        [orderId] [int] NULL,
        [productid] [int] NULL,
        [productcategoryid] [int] NULL,
        [distributorid] [int] NULL,
        [ordertotal] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [orderrequestedtime] [datetime] NULL,
        [orderapprovedtime] [datetime] NULL,
        [quantity] [int] NULL,
        [productname] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [producttype] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ordernumber] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [latestchargedbackamount] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [serialnumber] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [timestamp] [datetime] NULL,
        [totalchargebackamount] [decimal](18, 2) NULL,
        [paymentstatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [accountstatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [deliverymethod] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [privatekey] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_ordersHistory2008_S] PRIMARY KEY CLUSTERED
    (
        [orderhistoryid] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]


    ALTER TABLE [ordersHistory2008_S]  WITH CHECK ADD  CONSTRAINT [FK_ordersHistory2008_S_orders2008_S] FOREIGN KEY([orderId])
    REFERENCES [orders2008_S] ([orderid])
    Friday, June 27, 2008 3:06 AM
  • It's this line:

     

    Code Snippet

    Insert into [ordersHistory2008_S] values

    (@NOId,@productId,@productCategoryId,

    @distributorId,@ordertotal,@orderrequestedtime,'',

    @quantity,@productname,@producttype,'','','','','','PENDING','PRE-ACTIVE',@deliverymethod

    ,'')

     

     

     

    You're trying to insert '' into a decimal field (totalbackchargeamount).  Try changing it to:

     

    Code Snippet

    Insert into [ordersHistory2008_S] values

    (@NOId,@productId,@productCategoryId,

    @distributorId,@ordertotal,@orderrequestedtime,'',

    @quantity,@productname,@producttype,'','','','',0,'PENDING','PRE-ACTIVE',@deliverymethod

    ,'')

     

     

    Let me know how you fare.
    Friday, June 27, 2008 3:17 AM
    Moderator
  • YES IT WORKED, MANY MANY THANKS,

    I JUST OVERLOOKED IT, I WAS INSERTING NULL STRING INTO DECIMAL.

    --- ALSO, MY newOrderHistoryId IS NULL, ANY IDEA???

    THIS LINES ( IT CREATES THIS IDENTITY VALUE IN TABLE, BUT THE BELOW SELECT IS READING NULL, I GUESS)

    set @newOrderHistoryId =  (select IDENT_CURRENT('orderHistory2008_S'))
    set @NHOId = @newOrderHistoryId

    ---- ALSO, WHY IT ASKS FOR INPUT VALUES FOR OUTPUT VARIABLES.

    THANKS,




    Friday, June 27, 2008 3:29 AM
  • You're very welcome.  As for newOrderHistoryID, are records getting inserted into WebPurchasedPlan2008_S?  Perhaps it's hitting your GoTo before it runs the Ident_Current query.  Otherwise, it could be a metadata visibility issue (your account not having sufficient rights to properly query ident_current.  Check out the MSDN article on IDENT_CURRENT.  In your case, you may want to use @@IDENTITY OR SCOPE_IDENTITY instead of IDENT_CURRENT.  If another process inserts a record into your table AFTER your insert but BEFORE your IDENT_CURRENT, you won't get the record ID of the row you inserted.

     

    SQL is asking you to provide output variables so it can output what you've requested.  Are you not looking to get that information back from the proc?

    Friday, June 27, 2008 4:35 AM
    Moderator
  • Strange thing about is that it has rows values for identify column in orderhistory2008_S table, but when I run this

     

    select IDENT_CURRENT('orderHistory2008_S')

     

    or select IDENT_CURRENT('DBNAME.SCHEMA.orderhistory2008_S')

     

    both returns null.

     

    it is not hitting GOTO statements becuase it is inserting records in target table (WebPurchasedPlan2008_S but inserted value for neworderhistoryId = null, because that is what read from source table as i explained above.

     

    same thign we are doing for orders2008_S table, but it is ok for that.

     

    ---------------------------------

    1) Insert record in NewOrder table. -- New identity is created (OK)

    2) Read Identity value (OK)

    3) Insert into historyTable (OK) --- New identity is created (OK)

    4) Read Identity value from number 3), --- READS null.  ------------------- Problem is born here.

     

     

     

    Thanks for your continued help.

    Friday, June 27, 2008 1:45 PM
  • For now, I had it working with @@Identity....!

     

    thanks,

     

    Friday, June 27, 2008 2:07 PM