Published on: 25 September, 2017

How to check if a function exists in Sql Server?

Posted by andy | 306 views 0 likes 0 favourites 0 comments
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 does not exist then perform a creation of the function.

IF NOT 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' ))
    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 

The condition to check should apply as well for dropping a function. You can use the following checking procedure. The only difference with above checking is we do not use the keyword NOT as we need to check if the function method exists.

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 

If you need to perform an update of a function, the way to check is pretty much the same with checking if a function already exists. The difference is the we have to use keyword ALTER rather than CREATE.

IF NOT 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' ))
    ALTERFUNCTION [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 
Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.
Share your article with us and add your own google adsense account to earn extra money. Plus, you can promote a link back to your site.
Related Articles
Published on: 12 May, 2016
Do you need to sort comments with nested replies in SQL Server? Well this can be easily done by combining the same table using the UNION ALL join to populate the comments content. There is a trick to used to sort the comments which use the combination of a record ID field with the row number index.
Published on: 14 April, 2016
Before running an SQL script to create a table dynamically, it would be a good step if you perform a checking if a table already exists. If it does, you may want to skip creating the table otherwise you will get an error exception.
Related Tutorials
Published on: 27 September, 2013
Learn what is UpperCase function in SQL server and how to use UCASE() in sql query.
Published on: 26 September, 2013
Learn what is LCASE() function in SQL server and how to use LCASE() in sql query.
Published on: 25 September, 2013
Learn what is LEN() function in SQL server and how to use LEN() in sql query.
Published on: 24 September, 2013
Learn what is COUNT() function in SQL server and how to use COUNT() in sql query.
Published on: 23 September, 2013
Learn what is AVG() function in SQL server and how to use AVG() in sql query.