SQL Server FAQ - "ALTER FUNCTION" - Modifying Existing Functions

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Modify an Existing User Defined Function?

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:

USE FyiCenterData;
GO

-- 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?

(Continued on next topic...)