Using UDF as a default value for a column

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

Using UDF as a default value for a column

Author

Topic

JLM
Starting Member

6 Posts

Posted - 2006-10-17 : 15:00:22

I have a UDF that returns a char(10) random value. This runs fine when calling it directly via Query Analyzer, Stored Procs, etc.

I now want to create a new column in a table (via the Table Designer) and want to set the default value to the function. I have entered dbo.f_myfunction() for "Default Value", but I get an error:

Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

The function is in the same database as the table. This is all on my local database where I am the admin.

I have also tried database.dbo.f_myfunction(), but I get an Invalid Object error.

Any help would be appreciated. Thank you,

JLM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 15:08:02

How does the function generate a random string?

You should post the code for the function.

CODO ERGO SUM

JLM
Starting Member

6 Posts

Posted - 2006-10-17 : 15:19:37

The function itself is fine. My question is how do I call the function to create the default value for a column in a table (i.e. for each new row, I want to set the default value to the value of the function, much like you do with getdate())? Example of data returned from function: hzRZlmobKi

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 15:24:55

OK, if you don't feel like posting the function, then good luck.

CODO ERGO SUM

JLM
Starting Member

6 Posts

Posted - 2006-10-17 : 15:28:29

Fine. Lets say the UDF is:
--------------------------------------------------
CREATE Function dbo.f_myfunction ()
returns char(10)

AS
begin
return 'hzRZlmobKi'
end
--------------------------------------------------

Now - how do I define this as the default value for column 'rand' in my 't_tokens' table?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 15:36:00

The problem is that your code in the function is what is causing the problem, which is why you'll have to post it. The way you gave it, it works fine,


create table t_tokens
([rand] char(10) default(dbo.f_myfunction ()))

insert t_tokens values (default)

select * from t_tokens

Result


rand        
---------- 
hzRZlmobKi

(1 row(s) affected)

JLM
Starting Member

6 Posts

Posted - 2006-10-17 : 15:49:01

I figured out the issue and wanted to post the answer in case anyone else wants to know how to use a UDF as a Default Value when you create your table via Enterprise Manager using the Design Table.

You want to make sure you put parenthesis around the function. Your Default Value should look like this:

([owner].[function]())

JLM

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 15:54:10

If there's not more to that, they why not just have it as a default value?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 16:11:09

Because there is more to his UDF, he just didn't want to post it.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 16:13:41

Who knows? Maybe he was ashamed of it.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 16:28:57

quote:
Originally posted by Michael Valentine Jones

Who knows? Maybe he was ashamed of it.

I think I would be...maybe he'll post back

What value to the row is a "random" value?

Are you looking for a unique key?

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

alderran
Starting Member

2 Posts

Posted - 2009-06-20 : 13:43:58

quote:
Originally posted by Michael Valentine Jones

Who knows? Maybe he was ashamed of it.

CODO ERGO SUM

why are there so many a**h*l*s on SQL Server forums? Seriously the % is quite high

alderran
Starting Member

2 Posts

Posted - 2009-06-20 : 13:45:43

quote:
Originally posted by JLM

I figured out the issue and wanted to post the answer in case anyone else wants to know how to use a UDF as a Default Value when you create your table via Enterprise Manager using the Design Table.

You want to make sure you put parenthesis around the function. Your Default Value should look like this:

([owner].[function]())

JLM

BTW thanks I had the same syntax question

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 18:43:55

quote:
Originally posted by JLM

I figured out the issue and wanted to post the answer in case anyone else wants to know how to use a UDF as a Default Value when you create your table via Enterprise Manager using the Design Table.

You want to make sure you put parenthesis around the function. Your Default Value should look like this:

([owner].[function]())

JLM

All joking aside, I'd sure like to see your random function. Any chance of you posting it?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:16:22

Alderran and JLM:
The use of enclosing paranthesises is documented very clearly in Books Online so there is no magic here.

JLM:
The error message suggests you are NOT using SQL Server Management Studio to alter your column to have a default value.
It would be nice if such information can be provided. I can read a few posts down your are using Enterprise Manager (for SQL Server 2000 ?)

The function resembles about Jeff's article how to translate an IDENTITY into a custom serial sequence as seen here
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-22 : 10:17:55

Jeff and Peso: JLM hasn't posted since 2006, so he probably isn't monitoring this thread.

This thread was just revived by alderran to let me know what an a**h*l* I am. Guess you can't make everyone happy.

CODO ERGO SUM

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-22 : 10:46:03

BWAA-HAA!!!! Shoot! I did it again. I always forget to look at the bloody dates on the posts. More coffee please!

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 11:31:08

Hahaha.. He revived a three year old thread?

E 12°55'05.63"
N 56°04'39.26"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-22 : 12:06:55

Nah... "alderan" did. I just followed suit.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Garf
Starting Member

1 Post

Posted - 2009-07-23 : 13:06:50

Thanks to JLM, for answering a question I had. Parentheses I knew you had to have, brackets, i did not.

To be asked to see a function when that wasn't the issue at all, and your responses, make me beleive MVJ has too much free time if he replies in such a condescending manner.

Its like saying hey, I have a problem with my ignition, the key doesn't fit, and you reply 'let me put your car up on the jack stand and take a look at your exhaust system'.

---------------------------------------------------------------------------
and yes, I registered just to post that inded, Michael Valentine Jones is an ass..

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-23 : 14:41:53

But, no one knew what the problem was except that the car wouldn't start. It very well could have been the UDF. I don't believe it was a condescending answer at all.

Also, with one post under your belt, I suggest you take a good healthy look at Michael's contributions before you go bad mouthing him. Heh... then you can bad mouth him.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources