CODEWITHSUNDEEP
sql-server-2008functionalter
user247702
user247702

Reputation: 24222

SQL alter recursive function

I'm using SQL Server 2008.

I want to execute an ALTER on a recursive function. The alter adds parameters, it seems to choke on that.

The function has no dependencies so I can safely do a DROP+CREATE, but what should be done when the function does have dependencies?

The error message is this one

Msg 8144, Level 16, State 2, Server TESTSERVER, Procedure fn_IsOwnerFunction, Line 177 Procedure or function dbo.fn_IsOwnerFunction has too many arguments specified.

The message appears a few more times, each time for a line where the function refers to itself. Note: it refers to its new version that has more parameters. After DROP+CREATE, the ALTER script works without errors.

Upvotes: 0

Views: 892

Answers (2)

Dzejms
Dzejms

Reputation: 3258

If the error is "too many arguments specified" then your calling code is passing in too many parameters. This could mean that the ALTER FUNCTION statement you ran earlier didn't actually process fully. That could be because of a syntax error or something similar. Run the Alter statement again and check the error message there.

EDIT:

Add a DROP FUNCTION statement before your CREATE FUNCTION statement:

if exists (select * from information_schema.routines Where routine_name = 'udf_FunctionName') 
drop function udf_FunctionName
GO

CREATE FUNCTION [dbo].[udf_FunctionName]

...

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

It is ok to drop a function that is used from other functions or stored procedures. Of course, after you drop/create the function you need to alter the functions and stored procedures that uses the function to add the new parameters.

Upvotes: 1

Related Questions