MS SQL Server Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

How To Modify an Existing User Defined Function?

MS SQL Server Interview Question
MS SQL Server Interview Question

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?

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Get the Definition of a User Defined Function Back?How To Create User Defined Functions with Parameters?