Setting default value for field in table??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Setting default value for field in table???

Author

Topic

bmassey
Starting Member

22 Posts

Posted - 2004-10-08 : 09:23:06

I am using SQL 2000 and trying to find a way to set-up a custom default value. I have a stored proc that randomly generates a 5 char (alpha/numeric) value that is useds as a unique identifier. Is there a way in Enterprise Manager to have the default returned from my stored proc so that anytime a new record is inserted this id will automatically be generated?

Jaap
Starting Member

19 Posts

Posted - 2004-10-08 : 09:50:08

A custom default is, in my opinion, not posible.

It seems to me that the easiest way to do this is make an after insert trigger that executes your procedure and fills the field.

Jaap

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-08 : 09:52:17

use a trigger for that.

Go with the flow & have fun! Else fight the flow

bmassey
Starting Member

22 Posts

Posted - 2004-10-08 : 10:05:09

I thought about using a Trigger but was concerned about bulk inserts into the table so I was hoping there was a way to set it up as a default.

Thanks for the help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 10:13:21

You could use a UDF as the default value for a column. For example:


create function Test()
returns VarChar(100)
as
	begin
	return ('hey')
	end

go
create table Jeff (A int, B varchar(100) default dbo.Test())
go

insert into Jeff (A) VALUES (1)

select * from Jeff

go

drop table Jeff
drop function Test

Your existing stored proc, how is it guaranteed not to generate duplicates?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 10:20:42

if you use a UDF, by the way, functions like RAND() are not allow (it must be deterministic).

You can get around this by using a view, like this:


create View RandomNumber
as
	Select Rand() as Number
go

create function Test()
returns VarChar(100)
as
	begin
	return (select Number from RandomNumber)
	end

go

create table Jeff (A int, B varchar(100) default dbo.Test())
go



insert into Jeff (A) 
select 1 union
select 2 union
select 3

select * from Jeff

go

drop table Jeff
drop function Test
drop view RandomNumber

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-08 : 10:24:43

Dr. this is waaaaayyyyy cool!!!!!!!
didnt' know this can be done.
very usefull.

Go with the flow & have fun! Else fight the flow

bmassey
Starting Member

22 Posts

Posted - 2004-10-08 : 11:02:00

I have a table with one column containing all alpha numeric values (0-9 & A-Z). I am then randomly selecting 5 values by ordering by newid() to mix the order each time. Example:

select top 1 @a = Field01 from TestTable order by newid()
select top 1 @b = Field01 from TestTable order by newid()
select top 1 @c = Field01 from TestTable order by newid()
select top 1 @d = Field01 from TestTable order by newid()
select top 1 @e = Field01 from TestTable order by newid()

set @newID = @a + @b + @c + @d + @e

I then check to make sure that @newID is not already in the table. If it is then I loop back to the top and create a different ID. This process is repeated until I have an ID that is not already in the table.

This table shouldn't grow very large (less than 20000) so I would think the chances of even creating a random duplicate should be pretty small, considering there are over 60 million combinations I could come up with.

If anyone has a better way then I am open to suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources