[MSSQL] Stored procedure en uniqueidentify

[stylee]

Hallo mensen,

Ik heb een stored procedure die een aantal andere stored procedures aanroept, alles staat onder een transaction.

Nu zijn deze procedures aardig wat regels maar het probleem is simpel samen te vatten:

elke tabel heeft een uniqueidentifier als PK, een stored procedure insert iets in deze tabel en returnt de PK als output parameter

vereenvoudigd voorbeeld:

code:

Create Procedure sp_SetCreateAddress
    @Street1 varchar(50),
    @AddressID uniqueidentifier OUTPUT
AS

Set NoCount On

SELECT @AddressID = newid()
INSERT Address (AddressID, Street) VALUES (@AddressID, @Street)

GO

De 'primaire' stored proc roept de 'kleintjes dan als volgt aan'

code:

DECLARE @_AddressID uniqueidentifier
EXEC sp_SetCreateAddress @Street, @_AddressID

EXEC sp_DoeIetsMetAdres @_AddressID

Maar de query failt dan bij sp_DoeIetsMetAdres omdat de PK geen null kan zijn. Conclusie: @_AddressID is null.

Iemand enig idee wat ik fout doe, zal vast iets stoms zijn

Alvast bedankt

[whoami]

Wat doet die newid()?

Gebruik je niet beter als PK een integer veld dat een identity is, en dus autonummering is ipv een uniqueidentifier?
Dan kan je als volgt te werk gaan:

code:

CREATE PROCEDURE SP_CREATEADRESS ( @p_Street VARCHAR(50),
                                   @p_AdressId INTEGER OUTPUT )
AS
BEGIN
   INSERT INTO tblAdress ( Straat ) VALUES ( @p_Street )
   SELECT @p_AdressId = @@Identity FROM tblAdress
END

[ Voor 4% gewijzigd door whoami op 04-02-2003 19:34 ]

[stylee]

Het databasemodel is niet van mij en daar kan ik ook niks aan veranderen.

Die newid() genereert een GUID (schijnt behoorlijk uniek te zijn). Als ik in de query analyzer alleen een aantal stored procs uitvoer die geen FK afhankelijkheden heb, zie ik dat de INSERTS gewoon goed gaan (de GUID's worden netjes aangemaakt en de tabellen gevuld).

Dus het probleem zit hem in óf het teruggeven van uniqueidentities óf in het opslaan van uniqueidentities in lokale variabelen.

Denk ik tenminste

[EfBe]

en als je nu eens een local uniqueidentifier var definieert, daar de value van newid() aan toevoegt en die var meegeeft aan de insert. Je @AddressID is nl. een output parameter. Na de insert de val van de local var assignen aan @AddressID.

[Verwijderd]

moet het niet set zijn?

SET @AddressID = newid()
INSERT Address (AddressID, Street) VALUES (@AddressID, @Street)

SELECT @AddressID

[whoami]

Verwijderd schreef op 04 February 2003 @ 20:56:
moet het niet set zijn?

SET @AddressID = newid()
INSERT Address (AddressID, Street) VALUES (@AddressID, @Street)

SELECT @AddressID

SET is waarschijnlijk wel te preferen boven SELECT

[stylee]

Verwijderd schreef op 04 February 2003 @ 20:56:
moet het niet set zijn?

SET @AddressID = newid()
INSERT Address (AddressID, Street) VALUES (@AddressID, @Street)

SELECT @AddressID

Nope dat returnt ie gewoon de AddressID als resultaat, ik moet hem echt als output parameter hebben.

- Edit -

Mischien zou het wel als volgt kunnen werken:

SELECT _MyID = EXEC myStoredProc

Morgenochtend maar ff proberen.

[ Voor 17% gewijzigd door stylee op 04-02-2003 21:55 ]

[stylee]

EfBe schreef op 04 February 2003 @ 20:51:
en als je nu eens een local uniqueidentifier var definieert, daar de value van newid() aan toevoegt en die var meegeeft aan de insert. Je @AddressID is nl. een output parameter. Na de insert de val van de local var assignen aan @AddressID.

Dit werkt inderdaad, maar ik vind het nogsteeds raar dat het gewoon niet op die output parameter manier werkt.

Jammer, want nu kunnen mijn stored procs niet geheel zelfstandig hun eigen id's genereren.

[Annie]

Bij de aanroep van je sp moet je ook aangeven dat de variabele die je meegeeft een output var is, dus:

SQL:

DECLARE @_AddressID uniqueidentifier
EXEC sp_SetCreateAddress @Street, @_AddressID OUTPUT

EXEC sp_DoeIetsMetAdres @_AddressID

Zie ook de BOL.

whoami schreef op 04 February 2003 @ 21:00:

[...]


SET is waarschijnlijk wel te preferen boven SELECT

Waarom ?

[ Voor 27% gewijzigd door Annie op 04-02-2003 22:02 ]

[whoami]

Annie schreef op 04 February 2003 @ 22:01:

Waarom ?

Hmmm....
In de Books online staat het volgende bij de rubriek
select @localvariable:

It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.

Maar ik vind niet direct een beargumentering.... Ik herinner me wel eens dat een collega van me dat ook ooit es gezegd heeft, maar ik weet z'n argumenten niet meer.

[EfBe]

SET produceert sowieso geen resultset, select wel.

[Annie]

afaik produceert een SELECT @var = 'iets' ook geen resultset en BOL zegt er dit over:

Note A SELECT statement that contains a variable assignment cannot also be used to perform normal result set retrieval operations.

Wie heeft er gelijk