stored procedure to copy a record

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bonggaspar Valued Member Points: 74 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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 · Answer 3

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