stored procedure to copy a record

• rosarozina

  SSC-Addicted

  Points: 484

  More actions

  July 31, 2017 at 11:18 am

  #343787

  trying to create a stored procedure to copy a record in the same table where the reference number is reference number  +1 of the last reference number and the ID also increases .
  here is the stored procedure i used and when i try to execute the procedure, it gave me an error saying Error converting data type nvarchar to int.
  ALTER PROCEDURE [dbo].[ItemSubPart_TEST1]
  ( @ItemSubPartID int
  , @ItemID_New int
  , @ItemSubPartID_New int OUTPUT)
  AS
  BEGIN
      --Get the next available reference number
      DECLARE @ReferenceNumber int;
      SET @ReferenceNumber = (select max(ReferenceNumber) + 1 FROM tblItemSubParts)

      IF (ISNULL(@ItemID_New, 0) = 0)
          BEGIN
          
              SELECT @ReferenceNumber
              FROM tblItemSubParts
              WHERE ItemID=(SELECT ItemID FROM tblItemSubParts WHERE ItemSubPartID=@ItemSubPartID)
          
          END
      ELSE
          BEGIN
              SELECT @ReferenceNumber
              FROM tblItemSubParts
              WHERE ItemID=@ItemID_New
              
          END

     
      INSERT INTO tblItemSubParts (ItemID, PartName,  Cost, ReferenceNumber,   Width, Thickness, Length, SupplierNumber)
                                       
      SELECT @ItemID_New, PartName, Cost,  @ReferenceNumber, Width, Thickness, Length,  SupplierNumber
      FROM tblItemSubParts
      WHERE ItemSubPartID=@ItemSubPartID

    
      SET @ItemSubPartID_New = SCOPE_IDENTITY();
      
      END

• bonggaspar

  Valued Member

  Points: 74

  More actions

  August 2, 2017 at 3:10 pm

  #1953854

  Can you please provide your table structure, data sample and expected result?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 2, 2017 at 3:18 pm

  #1953857

  Table definitions please. There's no way to debug a data type conversion error without knowing what the data types are.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• rosarozina

  SSC-Addicted

  Points: 484

  More actions

  August 2, 2017 at 4:26 pm

  #1953864

  thanks for the reply but I found out that the problem was during execution. This is the correct one
  Declare @ItemSubPartID_New int
  Exec [dbo].[ItemSubPart_TEST1] @ItemSubPartID = 464, @ItemID_New = 77, @ItemSubPartID_New = @ItemSubPartID_New output
  Select @ItemSubPartID_New

  What I used was
  Declare @ItemSubPartID_New int
  Exec [dbo].[ItemSubPart_TEST1] @ItemSubPartID = 464, @ItemID_New = 77, @ItemSubPartID_New =  output
  Select @ItemSubPartID_New