变量不工作的存储过程

Question

I call the procedure with php and the relevant variables. I need the latest IDs to use it for the next insert, so I set variables with SCOPE_IDENTITY. The return ist always the value of appointment_id ?!

ALTER proc [dbo].[insertPersonWithCmoFmo]
@appointment_id int,
@kostenstelle varchar(50),
@vorname varchar(50),
@nachname varchar(50),

@ci_nummer int,
@anzahl_monitore_old int,
@raum varchar(50),
@gebäude varchar(50),
@bemerkung text,

@hardware_typ varchar(50),
@anzahl_monitore_new varchar(50),
@zubehör text

as

DECLARE
@latestPersonID int,
@latestCmoID int,
@latestFmoID int

BEGIN

    INSERT INTO [RC.Persons] (kostenstelle, vorname, nachname) VALUES (@kostenstelle, @vorname, @nachname);
    SET @latestPersonID = (SELECT SCOPE_IDENTITY())

    INSERT INTO [RC.CMO] (ci_nummer, anzahl_monitore, raum, gebäude, bemerkung) values (@ci_nummer, @anzahl_monitore_old, @raum, @gebäude, @bemerkung);
    SET @latestCmoID = (SELECT SCOPE_IDENTITY())

    INSERT INTO [RC.FMO] (hardware_typ, anzahl_monitore, zubehör) values (@hardware_typ, @anzahl_monitore_new, @zubehör);
    SET @latestFmoID = (SELECT SCOPE_IDENTITY())

    INSERT INTO [RC.Appointments_RC.CMO] (cmo_id, appointment_id) values (@latestCmoID, @appointment_id);

    INSERT INTO [RC.Persons_RC.CMO] (cmo_id, person_id) VALUES (@latestCmoID, @latestPersonID);

    INSERT INTO [RC.Persons_RC.FMO] (fmo_id, person_id) VALUES (@latestFmoID, @latestPersonID);

    return @latestFmoID

END

This is the exec code. Why the is a "N" before all varchar type?

USE [Testtable]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[insertPersonWithCmoFmo]
        @appointment_id = 52,
        @kostenstelle = N'54',
        @vorname = N'testname',
        @nachname = N'testlastname',
        @ci_nummer = 111222333,
        @anzahl_monitore_old = 2,
        @raum = N'255',
        @gebäude = N'KWA12',
        @bemerkung = N'blablabla',
        @hardware_typ = N'Desktop',
        @anzahl_monitore_new = N'4',
        @zubehör = N'Test'

SELECT  'Return Value' = @return_value

GO

SQL Output: Meldung 2601, Ebene 14, Status 1, Prozedur insertPersonWithCmoFmo, Zeile 36 Cannot insert duplicate key row in object 'dbo.RC.FMO' with unique index 'NonClusteredIndex-20140116-143317'. The duplicate key value is (). The statement has been terminated.

Answer 1

Is there something about the error message you don't understand? One or more of the tables has a unique constraint (or index) and you are trying to insert the same values in the table. For example, the persons table might already have the person in it.

The N before the string explicitly makes the string use wide characters.

Your stored procedure probably needs to be rewritten. You need to check errors that might occur along the way. Traditionally, a value would be returned using an OUTPUT parameter.

The safest way to get the new id value is to use the output clause of the insert statement (see the documentation here).