Default Value for a field in a table

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)

Default Value for a field in a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-05 : 08:55:00

Andy writes "I have a table that I would like to assign an ID number to a changeID field. I don't want to use the identity feature because the table contains different groups and the identity feature will sequentially assign a number accross the groups.

I want the ID number to be sequential with in the group.

I wrote a user defined function

CREATE FUNCTION ChangeID
(@GroupName int)
RETURNS int
AS
BEGIN
DECLARE @returnID int
SET @returnID = (SELECT Max(ChangeID)+1 FROM tblAddendum WHERE projectID = @GroupName)
return @returnID
END

However I can't use this in the default value field when designing the table. I just want to make sure that when new record is loaded it gets the next sequential ID number for its grouping. I thought a userdefined function was the way to go.. Is this better suited for a trigger?

Any Ideas? Since I use this via an ASP page I could just write a routine that calculates it and places it in.. however it would be nicer if this could happen at the Database leve.

Thanks for any help or direction you can give"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-05 : 08:57:51

It's probably easier to use an identity column, but not use it for the group sequence. Something like this:

create table _a(id int not null identity(1,1), projectid varchar(2) not null)
go

create function dbo.seq(@projectid varchar(2), @id int) returns int as
begin
declare @c int
select @c=count(*) from _a where projectid=@projectid and id<=@id
return @c
end
go

alter table _a add seq as dbo.seq(projectid, id)
go

insert _a(projectid) values('A')
insert _a(projectid) values('A')
insert _a(projectid) values('A')
insert _a(projectid) values('B')
insert _a(projectid) values('B')
insert _a(projectid) values('C')

select * from _a

The identity is basically a dummy column that's only there to provide a way to count rows to generate a sequence.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-05 : 11:13:45

You could use an instead of trigger so that you could catch the data on the way in, and then populate the value for your group based ID field.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-05 : 16:48:41

That's a good idea too. You'd also need to have it run for delete operations. You could probably do it using a regular AFTER trigger as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources