Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
kyHi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.