Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER User Defined Functions in One-Go in SQL Server for Easy Maintenance
I had written a blog post on An Elegant way to CREATE or ALTER Stored Procedures in SQL Server in One-Go for Easy Maintenance and it received an overwhelming response and also a couple of questions around how to go about doing the same thing for Functions and Views in SQL Server. In this post, let us look at how to CREATE or ALTER User Defined Functions in SQL Server in One-Go so that it is easy to maintain the scripts.
Usual approach that developers tend to take is the easy route and put a check to see if the Function Exists. If Function Exists, drop it and then create it with updated schema/logic. If it does not exist, create the Function. As discussed in the previous post on Stored Procedure, here are the drawbacks of DROP and RE-CREATE/CREATE approach:
DROP FUNCTION will fail if in the following scenarios:
- If there are other Transact-SQL functions or views in the database that reference this function and were created by using SCHEMABINDING
- If there are computed columns, CHECK constraints, or DEFAULT constraints in the database that reference the function
In addition to that, following are few drawbacks of using DROP and CREATE approach:
- Permissions associated with the object, like GRANT EXECUTE, SELECT etc., are lost when we drop and re-create the User Defined Functions.
- If ALTER FUNCTION Script is given during maintenance (to apply changes) then, different Script needs to be given while Creating the User Defined Function (on a new environment or while re-building an existing environment) and while Modifying the User Defined Function.
- If DROP FUNCTION and CREATE FUNCTION approach is used, then all the permissions previously present on the User Defined Function need to be given again with the help of necessary scripts.
To address the drawbacks highlighted above, there is an elegant way of Creating and Modifying a User Defined Function similar to Stored Procedures using a Single T-SQL Script and the same script can be executed multiple times on any environment – whether it is a new environment or an existing environment and you are applying some changes to the Function.
Here is a sample script to CREATE/ATLER a User Defined Function.
IF OBJECT_ID('dbo.udfGetSumOfIntegers') IS NULL -- Check if UDF Exists EXEC('CREATE FUNCTION dbo.udfGetSumOfIntegers (@inputInt1 INT) RETURNS INT AS BEGIN RETURN 1 END;') -- Create Dummy UDF GO ALTER FUNCTION dbo.udfGetSumOfIntegers -- Alter the UDF Always ( @inputInt1 INT , @inputInt2 INT ) RETURNS VARCHAR(255) AS BEGIN DECLARE @outputSum INT SET @outputSum = @inputInt1 + @inputInt2; RETURN ('Number ' + CAST(@outputSum AS VARCHAR(255))) END GO SELECT dbo.udfGetSumOfIntegers(1,2) AS OutputSum GO -- © Dattatrey Sindol (http://dattatreysindol.com
The output of the above function would be as shown below.
Please note that, in the above example, I have intentionally kept only one input variable in the CREATE FUNCTION statement just to demonstrate that the number of variables can be increased as part of ALTER FUNCTION statement. Similarly, the data type of output can be changed like in the above example, it was changed from INT in CREATE FUNCTION statement to VARCHAR(255) in ALTER FUNCTION statement.
Following are few highlights of this approach:
- This approach cannot be used to ALTER a scalar-valued function in to a table-valued function, or vice versa
- This approach cannot be used to ALTER an inline function in to a multi-statement function, or vice versa
- This approach cannot be used to ALTER a Transact-SQL function to a CLR function or vice-versa
Let me know if you know of a better way to handle this. Would be happy to learn different ways of doing it.
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
Until next time, Happy Learning 🙂
Posted on October 1, 2015, in Best Practices, Code Snippets, Development Tips, SQL Server, SQL Server BI, T-SQL, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged Best Practices, Databases, Microsoft SQL Server, MSBI, SQL Server, T-SQL, Tips, Tips and Tricks, Tricks. Bookmark the permalink. Leave a comment.