Sende data inn og ut av en MS SQL database samtidig

Når man lager prosedyrer for å legge inn, endre og slette data vil man ofte ha data ut igjen i samme slengen. Det kan gjøres på flere forskjellige måter i SQL 2005. Her ser vi på 3 ulike måter å gjøre dete på.

For å efektivisere kommunikasjonen med databasen mest mulig bør man der man kan ta ut data i samme operasjon som man setter inn data der dette er nødvendig. Man skal feks. sette inn en ny rad og trenger id’en til denne raden rett etter man har sendt data til tabellen. Den stygge måten å gjøre dette på er å sende inn data og deretter spørre etter SELECT TOP 1 Id FROM Products ORDER BY Id DESC etterpå. Dette medfører at man får to ulike connections til database. En til å legge inn data og en for å lese av den siste id’en etterpå.

I SQL Server 2005 har det kommet en del nye muligheter og en av de er bruk av OUTPUT som kan brukes ved DELETEINSERT og UPDATE. Syntaxen er enkel i bruk og leges bare til den eksisterende koden og den brukes omtrendt som en SELECT.

OUTPUT / INSERTED

I dette eksempelet bruker vi for enkelhets skyld en tabell med kun to kolonner. En kolonne “ProductId” (Primary Key) og en med tekst “ProductName“.

-- Her er tabellen fra eksempelet: 
CREATE TABLE Products 
(
 ProductId INT NOT NULL IDENTITY(1, 1),
 ProductName VarChar(255) NOT NULL
)

Hvis man nå vil lagre data til denne tabellen og samtidig ta ut ProductId kan man gjøre dette med følgende SQL-setning:

INSERT INTO Products (ProductName) 
OUTPUT INSERTED.ProductId 
VALUES ('Visual Studio 2005')

Koden vil sette inn en ny rad i tabellen og returnere den nye ProductId. Man kan også legge til INSERTED.ProductName for å få returnert produktnavnet også.

INSERT INTO Products (ProductName)
OUTPUT INSERTED.ProductId, INSERTED.ProductName
VALUES ('Visual Studio 2005')

Man kan med andre ord returnere de data man ønsker fra den raden som ble lagret.

@@IDENTITY, SCOPE_IDENTITY() og IDENT_CURRENT()

En annen måte å gjøre dette på er vha @@IDENTITY, SCOPE_IDENTITY() eller IDENT_CURRENT(). Disse to funksjonene returenere kun primery key fra den siste raden som ble lagret.

SELECT @@IDENTITY
Dette er kanskje den meste brukte funksjonen for å ta ut siste Id. Denne returnerer siste IDENTITY verdi fra en connection uavhengig av hvilken tabell eller hvilket scope som produserte verdien.

SELECT IDENT_CURRENT(‘Products’)
Denne nye funksjonen returnerer siste produserte IDENTITY i en angitt tabell uavhengig av hvilket scope som produserte verdien.

SELECT SCOPE_IDENTITY()
Denne nye funksjonen returnerer siste produserte IDENTITY i det scope man befinner seg i. Dermed er dette den beste måten å ta med seg en Id ut når man lagrer data.

INSERT INTO Products (ProductName)
VALUES ('Visual Studio 2005')
SELECT SCOPE_IDENTITY()