Tip

Writing T-SQL functions to SQL Server system schema

Sometimes when dealing with many applications that need similar functionality, the best thing to do is to write functions directly to the SQL Server system's schema.

    Requires Free Membership to View

This enables all databases within the server to execute the function as though it was a regular expansion of the T-SQL language.

Here I describe the way to do it. I used the example of a mathematical function to solve the equation Ax^2 + Bx + C = 0 and constructed a T-SQL function called fn_qsol to solve it.

The first thing to do is to enable the server to allow system schema updates, by executing:

USE master
GO

-- allow system updates in server
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Next comes the function's source code:

-- create a matematical function 
-- for example : solution for the parabolic equation
-- aX^2 + bX + c = 0
-- function gets A,B,C coefficients and the sign '+' or '-' to give
--  the first or second solution.
-- if  A=0 or no solution exists (B^2 < 4AC)  NULL  is returned

CREATE FUNCTION system_function_schema.fn_qsol 

      (@a real, @b  real, @c  real, @sign char(1))
    RETURNS real 
AS
BEGIN
    Declare @sgn real;                           
    IF ( ((@b * @b - 4*@a*@c) < 0) or 
         (@a = 0) ) RETURN (null)
    else
      begin
        if (@sign = '+') set @sgn = +1.0
        else set @sgn = -1.0
      end
      return ((-1 * @b + @sgn * sqrt (@b * @b - 4*@a*@c)) /  (2*@a))   
END
Go

Next thing is to disable changes to the system's schema again.

EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE 
GO

Example of a call to the function

-- Invoke the function
USE pubs
GO
SELECT fn_qsol (1,-1,-1,'+') , fn_qsol (1,-1,-1,'-')

This gives us the solution 1.618034 (also known as the "Golden ratio") and -0.618034 (1 – "Golden ratio").

Conclusion

The process I showed here can be used as a tool for designing common functionality for use by many applications that use the same SQL Server. This allows the sharing of code and reduces coding costs.

About the Author

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)
 

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.