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
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").
ConclusionThe 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: firstname.lastname@example.org)
This was first published in February 2005