SQL Server/T-SQL Tutorial/Sequence Indentity/SCOPE IDENTITY — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:46, 26 мая 2010
Last identity value generated for any table in the current session, for the current scope
<source lang="sql">
5> SELECT SCOPE_IDENTITY() 6></source>
SELECT SCOPE_IDENTITY()
<source lang="sql">
1> CREATE TABLE customer 2> ( 3> cust_id smallint IDENTITY(100, 20) NOT NULL, 4> cust_name varchar(50) NOT NULL 5> ) 6> GO 1> 2> INSERT customer (cust_name) VALUES ("AAA Gadgets") 3> SELECT SCOPE_IDENTITY() 4> GO (1 rows affected)
100
(1 rows affected) 1> 2> drop table customer; 3> GO</source>
Using the SCOPE_IDENTITY() Function
<source lang="sql">
SCOPE_IDENTITY() returns the last IDENTITY value inserted by your session into any table that has an IDENTITY column, but only in the current scope. A scope is a batch, a stored procedure, a trigger, or a function. 7> 8> CREATE TABLE MyTable ( 9> key_col int NOT NULL IDENTITY (1,1), 10> abc char(1) NOT NULL 11> ) 12> INSERT INTO MyTable VALUES ("a") 13> INSERT INTO MyTable VALUES ("b") 14> INSERT INTO MyTable VALUES ("c") 15> SELECT * FROM MyTable ORDER BY key_col 16> 17> 18> 19> DECLARE @mylastident AS int 20> SET @mylastident = SCOPE_IDENTITY () 21> PRINT @mylastident 22> 23> drop table MyTable 24> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a 2 b 3 c
(3 rows affected) 3 1></source>