Published on: 25 September, 2017

How to check if a function exists in Sql Server?

Tumblr Digg Google Plus

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 March, 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 March, 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.