Cannot change referenced UDF - 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 2005 Forums

Transact-SQL (2005)

Cannot change referenced UDF

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-17 : 08:52:03

I have a UDF in my database like so...

ALTER FUNCTION [dbo].[f_spn_GetCustomerNo] 
(
	-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
	RETURN 12441
END

This function is used as the default for a Customer_No column in every table. Now I want to change the UDF to RETURN 11564 but when I attempt to Alter Function I get the error:

Cannot ALTER 'dbo.f_spn_GetCustomerNo' because it is being referenced by object 'DF__tm_spn_eq__Custo__5C6CB6D7'.

which is a table that is using the UDF as a default. Is there any way around this with the exception of removing the default from every table, changing my function, and then resetting the default?

TIA!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:00:33

No. You have to ALTER the table, alter the function and alter the table again.

E 12°55'05.25"
N 56°04'39.16"

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-17 : 09:07:53

Bummer.

Is there a way to "DROP DEFAULT" on a table using T-SQL when not knowing the name of the default constraint? I would like to use SP_FOREACHTABLE to drop the defaults and then add them back in.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:20:46

ALTER TABLE
ALTER COLUMN MyDefaultColumn INT NULL
GO

ALTER FUNCTION ...
GO

ALTER TABLE
ALTER COLUMN MyDefaultColumn = dbo.MyFunction()
GO

E 12°55'05.25"
N 56°04'39.16"

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-17 : 09:47:07

The ALTER COLUMN MyDefaultColumn INT NULL does not drop the Default Constraint, so I still cannot ALTER Function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:53:00

See if you can make use of this query

SELECT	*
FROM	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

E 12°55'05.25"
N 56°04'39.16"

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-17 : 11:06:24

Thanks. I'm going to take a different approach where I will store the customer number in a Plant_Def table and have the UDF return the customer_no from that table. This way I will not have to change the UDF from client to client...

ALTER FUNCTION [dbo].[f_spn_GetCustomerNo] 
(
	-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
	RETURN SELECT MAX(Customer_No) From dbo.Plant_Def

Is there a better way to automatically insert a default from a column of another table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 12:31:49

quote:
Originally posted by Ken Blum

Thanks. I'm going to take a different approach where I will store the customer number in a Plant_Def table and have the UDF return the customer_no from that table. This way I will not have to change the UDF from client to client...

ALTER FUNCTION [dbo].[f_spn_GetCustomerNo]
(
-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
RETURN SELECT MAX(Customer_No) From dbo.Plant_Def

Is there a better way to automatically insert a default from a column of another table?

That can be done by means of a trigger but performance can hurt.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 12:44:08

And you should use IDENTITY column instead of MAX(CustomerNo)

E 12°55'05.25"
N 56°04'39.16"

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2008-06-17 : 12:58:18

I don't understand what you mean by IDENTITY. The plant_def table should always only contain 1 row. I only use MAX(Customer_No) in order to avoid "Accidents". The number will be the customer number from our ERP system. If I set that column as an IDENTITY type then I cannot assign the value correct?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 14:00:23

No, you cannot assign the value manually. Not easily anyway, but it's doable with SET IDENTITY_INSERT command.
SQL Server assigns the value for you when you use IDENTITY.

If you have no worries about concurrency, go ahead and use MAX(CustomerNo) and then add +1 and hope no one is doing the same thing the same time as you do.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources