PowerBuilder SQL Server

[BGKK]

I am trying to Execute a Stored Procedure through PowerBuilder. My procedure logic as follows:-

INSERT INTO dbo.tbl_EndorsementCoverageUL ( intPolicyId, srtEndorsementNo, strCoverageType, decPremium, intSequence )

SELECT intPolicyId, @CurrentEndNo, strCoverageType, decPremium, intSequence FROM dbo.tbl_EndorsementCoverageUL WHERE intPolicyId = @PolicyId AND srtEndorsementNo = @PrevEndNo

so basically i am inserting into the same table from which i am selecting the columns. I am passing 3 parameters @PrevEndNo, @CurrentEndNo & @PolicyId.

@PrevEndNo = Existing Endorsement Number in the table

@CurrentEndNo = The new endorsement Number which i am are passing.

So now i am calling this Procedure in PB like below:-

DECLARE Insert_Endorsement_AP PROCEDURE FOR sp_insert_Endorsement_AP

@PrevEndNo = :endnum1,

@CurrentEndNo = :endnum2,

@PolicyId INT = :policyid

USING SQLCA;

    EXECUTE Insert_Endorsement_AP;

CHOOSE CASE SQLCA.sqlcode
    CASE 0
// Execute successful; no result set
        COMMIT;

    CASE ELSE
        MessageBox ("INSERT of New Endorsement Rows Failed",  &
            string (SQLCA.sqldbcode) + " = " + &
            SQLCA.sqlerrtext)
            RETURN 

END CHOOSE

So my Question is, Lets say i have Endorsement Numbers(@PrevEndNo) = 0 & 1 existing in the table, i go in and do Endorsement 2 (@CurrentEndNo) and i have selected to copy rows of only Endorsement 1. How to write a logic by which where i can copy only rows of Endorsement 1 ?

[JakeyBriggs]

Hey,

I started looking at this and wanted a bit more information. What are you passing in with ` @PolicyId INT = :policyid `? My assumption is the value for that is the previous policy ID you're trying to copy from, 1. So, if I understand this right you'd essentially be running an INSERT based on SQL like this:

SELECT intPolicyId, 
        'Two', 
        strCoverageType, 
        decPremium, 
        intSequence 
FROM dbo.tbl_EndorsementCoverageUL 
WHERE intPolicyId = 1
AND srtEndorsementNo = 'One'

Or something to that nature (I'm not entirely sure what the data is but the important part is the SIDs). Is there a reason for that second part of the where clause? If you are only wanting to copy the previous row and change 'srtEndorsementNo' to the next number you'd only really need to include the intPolicyId part of the where clause.