"ALTER FUNCTION" - Modifying Existing Functions in SQL Server

Q

How To Modify an Existing User Defined Function in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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?

 

Creating User Defined Functions with Parameters in SQL Server

sys.sql_modules - Getting User Defined Function Definitions Back in SQL Server

Using User Defined Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2016-12-18, 1508🔥, 0💬