Writing T-SQL functions to SQL Server system schema

by
Eli Leiba

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

Step 2 of 2:

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: [email protected])

This was first published in February 2005

Step 3: Consider using implicit transactions

PRO+

Content

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

E-Handbook

Pricing out the new Windows Azure SQL Database

SearchBusinessAnalytics

Conclusion

About the Author

Dig Deeper

E-Handbook

0 comments

E-Mail

Username / Password

Password

Forgot Password?

Your password has been sent to: