This article has been triggered by a question from a customer asking about alternatives to using views for returning data into the interface. They wanted to know whether views were the only way in which data can be accessed.
I have looked at the use of stored procedures as an alternative before in the article "Working with a Stored Procedure that returns a result set".
Functions are another alternative to retrieving data. Functions are subroutines made up of one or more SQL statements that are used to bundle up code for reuse and MS SQL Server allows database administrators to create their own user-defined functions.
MS SQL Server actually supports three types of user-defined functions, scalar functions, inline table-valued functions and table-valued functions.
This article is not about user-defined functions in general, but rather I want to consider the usage of Table-Valued User-Defined Function within Sage CRM. If you want to know more about user-defined functions then the MS SQL online documentation is very good and quite accessible.
A table-valued user-defined function is designed to return data like a table and may be be very useful alternative to a view in certain circumstances.
Note: Both stored procedures and table-valued user defined function have important (and severe) limitations from a CRM perspective. The can't be used to return data to list blocks, chart blocks and reports and other structures because Sage CRM automatically changes the generated SQL statement sent to the database. It may do this to provide the count and paging mechanism for a list block or to control paging and security.
They may nevertheless have their uses. A table-valued user-defined function can be used where table or view expressions are used to fetch data in internal script and extensions (ASP & .NET). Views are limited to a single SELECT statement but user-defined functions can contain additional statements that allow more powerful logic than is possible in views.
The example below creates a very simple function. The job of the function is to bring data from the opportunities, cases and the communication table together to get a picture of a users current 'In Progress' activity.
[code language="sql"]
CREATE FUNCTION dbo.tvf_reportuseractivity (@userid AS int)
RETURNS TABLE
AS
RETURN
(
select count(oppo_opportunityid) as 'mycount', 'Opportunities' as 'mytype' from
opportunity where oppo_status = 'In Progress' and oppo_assigneduserid = @userid
union
select count(case_caseid) as 'mycount', 'Cases' as 'mytype' from cases
where case_status = 'In Progress' and case_assigneduserid = @userid
union
select count(cmli_commlinkid) as 'mycount', 'Communications' as 'mytype'
from vListCommunication where comm_status = 'Pending' and cmli_comm_userid = @userid
)
GO
[/code]
I can then access the function using an SQL statement.
[code language="sql"]
select * from dbo.tvf_reportuseractivity(3)
[/code]
I can then use this where-ever I would normally use an SQLQuery object. But notice the way in which parameters are passed is very different.
[code language="javascript"]
var mySQL = "select * from tvf_reportuseractivity("+intRecordID+")";
var myQuery = CRM.CreateQueryObj(mySQL,"");
myQuery.SelectSQL();
[/code]