Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Malaysia
    Posts
    17

    Post Unanswered: Stored procedure problem...

    hai friends,
    i am updating details products in to Products table through stored procedure "Products_Update". i am pasing all the values and criteria also, but its not updating the record and error is accuring. here is the stored procedure aand error msg.


    stored procedure:
    ------------------

    --
    -- Update Stored Procedure created by SP Generator on April 14-2003
    --
    CREATE procedure PRODUCTS_Update
    @ProductCode varchar(10),
    @ProductGroup varchar(50),
    @CoCode varchar(10),
    @Name varchar(100),
    @Category varchar(1),
    @ProductImage image,
    @Brand varchar(50),
    @CurrentPrice numeric(9),
    @Specifications varchar(100),
    @InStock numeric(9),
    @TotalOrdered numeric(9),
    @ExpectedDate datetime,
    @InDemand numeric(9),
    @ProductManager varchar(10),
    @Discount numeric(5),
    @NewArrival tinyint,
    @LaunchDate datetime,
    @CreateBy nvarchar(20),
    @CreateDate datetime,
    @ModifyBy nvarchar(20),
    @ModifyDate datetime
    as
    Declare @Rows int,
    @ErrNo Int,
    @ErrMsg varchar(255)
    Set Nocount off
    Begin Transaction

    Update PRODUCTS set ProductCode=@ProductCode,ProductGroup=@ProductGrou p,CoCode=@CoCode,Name=@Name,Category=@Category,Pro ductImage=@ProductImage,Brand=@Brand,CurrentPrice= @CurrentPrice,Specifications=@Specifications,InSto ck=@InStock,TotalOrdered=@TotalOrdered,ExpectedDat e=@ExpectedDate,InDemand=@InDemand,ProductManager= @ProductManager,Discount=@Discount,NewArrival=@New Arrival,LaunchDate=@LaunchDate,CreateBy=@CreateBy, CreateDate=@CreateDate,ModifyBy=@ModifyBy,ModifyDa te=CURRENT_TIMESTAMP where ProductCode=@ProductCode AND CoCode = @CoCode


    Select @ErrNo = @@error,@rows =@@rowcount
    If @Rows = 0
    Begin
    Select @ErrNo = 50001,@ErrMsg ='No Records Updated'
    Raiserror @ErrNo @ErrMsg
    GoTo ErrorHandler
    End

    If @ErrNo = 0
    Begin
    Commit Transaction
    Set Nocount on
    Return 0
    End
    Else
    Begin
    GoTo ErrorHandler
    End
    ErrorHandler:
    Rollback Transaction
    Set Nocount on
    Return @ErrNo
    GO



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

    and the error is:
    ------------------

    The 'PRODUCTS_Update' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.




    i am not understanding what is the error...

    pls help me.
    --------------------
    P.L.N.Prasad varma

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    If @ErrNo = 0
    Begin
    Commit Transaction
    Set Nocount on
    Return 0
    End
    Else
    Begin
    GoTo ErrorHandler
    End
    ErrorHandler:
    Rollback Transaction
    Set Nocount on
    Return @ErrNo
    GO
    isnt the line that says return 0
    doing what you told it to do?

  3. #3
    Join Date
    Mar 2004
    Location
    Malaysia
    Posts
    17

    Re: Stored procedure problem...

    Actually it should return 0 instead of null.

    now the stored procedure is returning null.

    how to tcheck weather stored procedure returns null or 0?

    and why SP is returning null. what si the problem with SP.

    any help will be appreciated.




    Originally posted by pln_verma
    hai friends,
    i am updating details products in to Products table through stored procedure "Products_Update". i am pasing all the values and criteria also, but its not updating the record and error is accuring. here is the stored procedure aand error msg.


    stored procedure:
    ------------------

    --
    -- Update Stored Procedure created by SP Generator on April 14-2003
    --
    CREATE procedure PRODUCTS_Update
    @ProductCode varchar(10),
    @ProductGroup varchar(50),
    @CoCode varchar(10),
    @Name varchar(100),
    @Category varchar(1),
    @ProductImage image,
    @Brand varchar(50),
    @CurrentPrice numeric(9),
    @Specifications varchar(100),
    @InStock numeric(9),
    @TotalOrdered numeric(9),
    @ExpectedDate datetime,
    @InDemand numeric(9),
    @ProductManager varchar(10),
    @Discount numeric(5),
    @NewArrival tinyint,
    @LaunchDate datetime,
    @CreateBy nvarchar(20),
    @CreateDate datetime,
    @ModifyBy nvarchar(20),
    @ModifyDate datetime
    as
    Declare @Rows int,
    @ErrNo Int,
    @ErrMsg varchar(255)
    Set Nocount off
    Begin Transaction

    Update PRODUCTS set ProductCode=@ProductCode,ProductGroup=@ProductGrou p,CoCode=@CoCode,Name=@Name,Category=@Category,Pro ductImage=@ProductImage,Brand=@Brand,CurrentPrice= @CurrentPrice,Specifications=@Specifications,InSto ck=@InStock,TotalOrdered=@TotalOrdered,ExpectedDat e=@ExpectedDate,InDemand=@InDemand,ProductManager= @ProductManager,Discount=@Discount,NewArrival=@New Arrival,LaunchDate=@LaunchDate,CreateBy=@CreateBy, CreateDate=@CreateDate,ModifyBy=@ModifyBy,ModifyDa te=CURRENT_TIMESTAMP where ProductCode=@ProductCode AND CoCode = @CoCode


    Select @ErrNo = @@error,@rows =@@rowcount
    If @Rows = 0
    Begin
    Select @ErrNo = 50001,@ErrMsg ='No Records Updated'
    Raiserror @ErrNo @ErrMsg
    GoTo ErrorHandler
    End

    If @ErrNo = 0
    Begin
    Commit Transaction
    Set Nocount on
    Return 0
    End
    Else
    Begin
    GoTo ErrorHandler
    End
    ErrorHandler:
    Rollback Transaction
    Set Nocount on
    Return @ErrNo
    GO



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

    and the error is:
    ------------------

    The 'PRODUCTS_Update' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.




    i am not understanding what is the error...

    pls help me.
    --------------------
    P.L.N.Prasad varma

Posting Permissions

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