Writing T-SQL functions to SQL Server system schema

Writing T-SQL functions to a SQL Server system schema for use by the entire server is explained.

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. 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 last published in February 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close