MS SQL Server Concepts and Programming Question:
How To Modify an Existing User Defined Function?
Answer:
If you find a mistake in an existing function previously created, you can drop (delete) it and create it again correctly. But dropping a function may affect other database objects who are depending on this function.
So the best way to correct a mistake in an existing function is to use the "ALTER FUNCTION" statement as shown in the following tutorial example:
-- Modifying an existing function
ALTER FUNCTION Sundays()
RETURNS INT
AS BEGIN
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2006-12-31';
SET @count = 0;
WHILE DATEPART(YEAR, @date) < 2008 BEGIN
SET @date = DATEADD(DAY, 1, @date);
IF DATENAME(WEEKDAY, @date) = 'Sunday'
SET @count = @count + 1;
END;
RETURN @count;
END;
GO
Command(s) completed successfully.
Do you know what correction has been made on this function?
So the best way to correct a mistake in an existing function is to use the "ALTER FUNCTION" statement as shown in the following tutorial example:
-- Modifying an existing function
ALTER FUNCTION Sundays()
RETURNS INT
AS BEGIN
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2006-12-31';
SET @count = 0;
WHILE DATEPART(YEAR, @date) < 2008 BEGIN
SET @date = DATEADD(DAY, 1, @date);
IF DATENAME(WEEKDAY, @date) = 'Sunday'
SET @count = @count + 1;
END;
RETURN @count;
END;
GO
Command(s) completed successfully.
Do you know what correction has been made on this function?
| Previous Question | Next Question |
| How To Get the Definition of a User Defined Function Back? | How To Create User Defined Functions with Parameters? |