Re: Getting the Number from the next automatic increment

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve _at_ BT (_at_)
Date: 04/25/04 

Date: Sun, 25 Apr 2004 11:24:33 +0000 (UTC)

Thanks Greg,
"Greg O" <greg-No-SPAM@No-SPAM.com> wrote in message
news:O4rJHpdKEHA.892@TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> Here is a extract from BOL
>
> IDENT_CURRENT is similar to the Microsoft® SQL ServerT 2000 identity
> functions SCOPE_IDENTITY and @@IDENTITY. All three functions return
> last-generated identity values. However, the scope and session on which
> 'last' is defined in each of these functions differ.
>
> a.. IDENT_CURRENT returns the last identity value generated for a
specific
> table in any session and any scope.
>
>
> b.. @@IDENTITY returns the last identity value generated for any table
in
> the current session, across all scopes.
>
>
> c.. SCOPE_IDENTITY returns the last identity value generated for any
table
> in the current session and the current scope.
>
> What this means is that normally people use @@identity like this
>
> insert into Call_log(Name) values('Fred');select SCOPE_IDENTITY()
>
>
> but if you have a trigger on the table that say inserts a row into a
> different table then it might have an identity as well. So the value from
> @@identity won't be what you think. So you can use
>
> insert into Call_log(Name) values('Fred');select
SCOPE_IDENTITY("Call_log")
>
> now if you wanted the identity from the other table that the trigger
updated
> you could do
>
> insert into Call_log(Name) values('Fred');select
> SCOPE_IDENTITY("TriggerupdatedTable")
>
> for more scripts go to http://www.SQL-Scripts.com
> --
> I hope this helps
> --------------------------------
> Greg O
> Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com
> Document all your databases now.
> http://www.ag-software.com/ags_scribe_index.aspx
> Crosstab queries from SQL Server.
> http://www.ag-software.com/xp_ags_crosstab.aspx
> Edit Extended Properties as easy as can be.
> http://www.ag-software.com/ags_SSEPE_index.aspx
>
>
> "Steve @ BT" <stevenriley@btopenworld.com> wrote in message
> news:c6d81u$ca0$1@hercules.btinternet.com...
> Hi All
> I have a Table which generats a automatic number when i add a new record
>
> how do i get that number that has been been added
>
> e.g. My table is
>
> CREATE TABLE [dbo].[Call_Log] (
> [Ref] SMALLINT NOT NULL IDENTITY(1,1),
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
>
> when i add a new record, how do i get the Ref number it has created.
>
> Thanks
> Steve
>
>
>

Relevant Pages

  • Re: Getting the Number from the next automatic increment
    ... IDENT_CURRENT is similar to the Microsoft® SQL ServerT 2000 identity ... table in any session and any scope. ... when i add a new record, how do i get the Ref number it has created. ...
    (microsoft.public.sqlserver.programming)
  • Summit Point Track Day Report
    ... It rained all morning, and George ... went out for our first session of the day. ... Steve chose not to. ... encouraging companion on the wet track. ...
    (rec.motorcycles)
  • Untold(...) variable scope in ASP.NET
    ... We all know that declaring a variable as 'Public' gives it global ... scope over the application, nothing new... ... I usually dont like to create session variable because i found them ... scope goes beyong the user session, actually you are better off using ...
    (microsoft.public.dotnet.languages.vb)
  • Re: FORTRAN 90 PARAMETER Scope
    ... Compilation complains about the 'common' block declaration if I use ... nothing has the scope of an include file. ... But basically, you'll need to give us, as Steve suggests, the actual ... Do notice, as Steve's reply illustrated, that your common block appears ...
    (comp.lang.fortran)
  • Re: How Can I Define Global Classes In Web Application ?
    ... which refers to scope. ... the child might think of "the world" as their neighborhood. ... This is entirely different than "Session Scope" which is restricted to those ... FTP Client Classes, Enhanced Data Controls, much more. ...
    (microsoft.public.dotnet.framework.aspnet)