Stored procedure with variables not working

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.

[Please use an output parameter or a resultset instead of `RETURN` for pulling data back from your procedure. `RETURN` is for error/status codes](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output). Also, don't use `single quotes` for alias delimiters - this syntax is deprecated in some scenarios, and makes your column look like a string literal. If you must use an alias that requires delimiting (hint: you should avoid it), use `[square brackets]`. — Aaron Bertrand, Jan 19 '14 at 17:53

score 1 · Accepted Answer · answered Jan 19 '14 at 16:25

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).

answered Jan 19 '14 at 16:25

Gordon Linoff

1,242,037
58
646
786

The ids are all auto increments. So I dont understand why there can be dublicate ids in a table. – mnlfischer Jan 19 '14 at 16:28
@mnlfischer . . . The duplicates are not on the auto incremented ids. There might be a unique index, for instance, on `hardware_typ, anzahl_monitore, zubehör` and you are inserting duplicates there. (The problem is on the `FMO` table.) – Gordon Linoff Jan 19 '14 at 16:46

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

The N indicates nvarchar instead of varchar.

See: https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements

edited May 23 '17 at 10:31

Community

1
1

answered Jan 19 '14 at 16:22

Alien Technology

1,760
1
20
30

Stored procedure with variables not working

2 Answers2