generate 1 added no - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

generate 1 added no

Author

Topic

cisjackie
Starting Member

3 Posts

Posted - 2006-12-07 : 04:29:57

hello guys,
as a beginner i want to calculate the last id number(which is auto generated) from a 'PLAYER' table. thats why i want to get the last id no, then add 1 with that value and again with other related data it will be saved in the table. this is my problem.

i wrote 2 sto. proc. which are below:
--------
CREATE PROCEDURE dbo.get_last_value_PLAYER
AS
RETURN (SELECT (COUNT(*)+1) FROM PLAYER)
----------
above one calculate and add 1 and return the value to the following proc.

----------
CREATE PROCEDURE dbo.insert_PLAYER
(@name VARCHAR(30), @country VARCHAR(30), @position VARCHAR(30))
AS
DECLARE @id int, @sql VARCHAR(500)
EXEC @id = dbo.get_last_value_PLAYER
SET @sql = INSERT INTO PLAYER (PID, PNAME, PCOUNTRY, PPOS)
VALUES (@id, @name, @country, @position)
EXEC @sql
----------

i didn't figure out my problems, would u pls help me?, would u pls show me any alternatives solution(if any)?
if u help me, pls do it soon.
thanx a lot..

hmmm...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 04:32:57

Your solution is good from the single-user point of view only. What happens when another user insert record in player in between the time gap you generate new value and insert it?

Why not use, IDENT_CURRENT() function to get last generated identity value?

Harsh Athalye
India.
"Nothing is Impossible"

cisjackie
Starting Member

3 Posts

Posted - 2006-12-07 : 04:52:57

hello harsh_athalye,

thanx for ur coop.
i just read that menthod by searching the shipped sql-server with .NET
can u give me some example? that will be preety much helpful.
is that the code seems like the following:
-----------
create proc dbo.test
as
declare @id int, @sql varchar(100)
@id = IDENT_CURRENT( 'PLAYER' )
set @id = @id+1
SET @sql = INSERT INTO PLAYER (PID, PNAME, PCOUNTRY, PPOS)
VALUES (@id, @name, @country, @position)
EXEC @sql
-----------
is the above query's syntax is OK? i'm totally novish in this track. don't be ferotious

hmmm...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 05:03:58

[code]create proc dbo.test
(
@id int out,
@name varchar(100),
@country varchar(40),
@position varchar(100)
)
as

INSERT INTO PLAYER (PNAME, PCOUNTRY, PPOS)
VALUES (@name, @country, @position)

select @id = scope_identity()[/code]

Peter Larsson
Helsingborg, Sweden

cisjackie
Starting Member

3 Posts

Posted - 2006-12-07 : 06:19:45

thanx peso. u * people are very helpful

hmmm...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-07 : 09:49:38

You have a major flaw in your logic: the count+1 from dbo.Player and the largest PID may not always be the same thing.

For your surrogate key (PID), use an identity column (like Peso's suggestion implies) and then you won't need to worry about generating ID numbers.

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources