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)
