Identity Field - 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

General SQL Server Forums

New to SQL Server Programming

Identity Field

Author

Topic

MDTerp90
Starting Member

1 Post

Posted - 2007-06-29 : 13:49:25

Hello...

New to SQL and have the following situation....

Example of table (currently)
PayID (Primary Key) PaymentNumber (Identity Field)
John 1
Tim 2
Chris 3
John (same as John above) 4
Jack 5
Steve 6

Would like to see it work this way
PayID (Primary Key) PaymentNumber (Identity Field)
John 1
John (same as John above) 2
Tim 1
Chris 1
Jack 1
Steve 1

Can the identity column be setup to start the counter over depending on value of PayeeID...If another Jack was added then the Payment Number would be 2 for Jack since there is already a record with value 1....

Hope this makes sense...

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-29 : 13:55:50

You can not use the identity option for this type of data. You will need to write your own custom routine to do this.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-29 : 15:13:07

[code]

CREATE TABLE myTable99 (
myName99 varchar(20)
, myId int
, PRIMARY KEY (myName99, myId)
)
GO

CREATE PROC mySproc99
@myName99 varchar(20)
AS
IF EXISTS (SELECT * FROM myTable99 WHERE myName99 = @myName99)
INSERT INTO myTable99(myName99, myId)
SELECT @myName99, MAX(myId) + 1
FROM myTable99
WHERE myName99 = @myName99
ELSE
INSERT INTO myTable99(myName99, myId)
SELECT @myName99, 1
GO

EXEC mySproc99 'John'

SELECT * FROM myTable99

EXEC mySproc99 'John'

SELECT * FROM myTable99

EXEC mySproc99 'Tim'

SELECT * FROM myTable99
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources