IMG-LOGO

How to check if a function exists in Sql Server?

andy - 25 Sep, 2017 11471 Views 1 Comment

When creating a function dynamically, it is a best practice to perform a check if a particular function you want to create has already existed in a database. To perform a check of an existing function you can use the following EXISTS keyword. If the function has already existed then you have to drop the function first and then perform a creation of the function.

IF EXISTS (SELECT * FROM   sys.objects WHERE  object_id = OBJECT_ID(N'[dbo].[my_function_name]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
    DROP FUNCTION [dbo].[my_function_name]
GO 

CREATE FUNCTION [dbo].[my_function_name](
       @ID NUMERIC(18,0)
    )
    RETURNS BIT
AS
    BEGIN
            DECLARE @NoOfRecords INT = 0
            SELECT @NoOfRecords = COUNT(*)
            FROM [dbo].ExampleTable
            WHERE ID = @ID

            IF @NoOfRecords > 0
                 BEGIN
                     RETURN 1
                 END 

            RETURN 0
    END

GO 

If you need to perform an update of a function, the way to check is pretty much the same as checking if a function already exists. You can re-drop the existing function and recreate it again..

Comments

Wayne Bortner
06 Mar, 2018
Have you seen this code working? I paste the first code into SQL Server Management Studio and get the result SQL Error(156): Wrong Syntax in the near of keyword FUNCTION
andy
08 Mar, 2018
Hi Wayne, Thanks for correcting the issue. It has to be done into a separated batch process. I have updated the article. It will perform a checking if the function has already existed, it will drop it first and then perform another action to create it.
Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...