SQL Server insert statement runs successfully but values are not added to table even though row is created

Question

I have an insert statement as below. I am able to debug my stored procedure and step through my code to verify that my code runs, but a funny thing happens.

The values that I expect to be saved in the database are not saved. Instead a row is created in the table and the RecordCreatedField is correctly created, even a primary key is included but the actual columns I expect to be inserted are not. All the columns are nvarchar/varchar, except for the date columns and the ID, but only those columns are populated. However the following @PRCUPC ,@PRCEAN ,@PRCGTIN,@PRCatalogNumber are somehow omitted.

EXEC spinsertprodcode 
                  @pid out, 
                  @ProdID, 
                  @UPC, 
                  @EAN, 
                  @TIN, 
                  @CNumber, 
                  0, 
                  0, 
                  1, 
                  @staid, 
                  @counter out



INSERT INTO dbo.ProdCodes
(PRProductID
,PRCUPC
,PRCEAN
,PRCGTIN
,PRCatalogNumber
,PRIsReplacement 
,PRIsReplaced 
,PRCRecordCreatedDate 
,PRCIsActive
,PRStatusFlag)

OUTPUT INSERTED.PRCID INTO @opID

VALUES
(@PRProductID, 
@PRCUPC, 
@PRCEAN, 
@PRCGTIN, 
@PRCatalogNumber,
@PRIsReplacement ,
@PRIsReplaced,
GETDATE(), 
@PRCIsActive, 
@PRStatusFlag)

SELECT @PRCID = O.ID FROm @opID O

Would appreciate any suggestion on how I can fix this or understand what is going on.

Here is an SQl fiddle of the main Stored proc that calls my insert code. SQL Fiddle of my code

Answer 1

@Kobojunkie: This kind of issues can be easily managed if you follow one of the golden rules of T-SQL Best Practices: use a TRY/CATCH block to wrap your code for INSERT and DELETE statements. Having an approapiate CATCH block including system variables like ERROR_NUMBER, ERROR_SEVERITY, ERROR_PROCEDURE, ERROR_LINE and ERROR_MESSAGE will give you clues about what's going wrong with the action being performed:

BEGIN CATCH
    SELECT @ErrorDetails = 
            N'Error raised when executing [Insert/Delete] statement: '
            + N' SQL Error: ' + ERROR_NUMBER()
            + N', Level: ' + ERROR_SEVERITY()
            + N', Line: ' + ERROR_LINE()
            + N', Message: ' + ERROR_MESSAGE() 
END CATCH

Hope this information helps to fix the issue within the sproc!