I have discussed calling Stored Procedures from within Sage CRM before in the the article "Using the COM CRM.CreateQueryObj() to call a Stored Procedure". From that article it can be seen that it is possible to call stored procedures where ever a QueryObject can be instantiated. This could include using code in a field's Create Script to populate the default value for the field on a newly created record. Or it could be used in a workflow javascript condition to return an value from a complex calculation that is then used to decide the availability of a workflow rule 'button'.

But the above article showed only the idea of calling a procedure that returned a single result. Within Sage CRM we frequently need to be able to work with sets of returned data. When working with a record object or indeed working with queryobject, both of these allow us to work with sets of results.

for example

var myQuery = CRM.CreateQueryObj("select * from company where comp_type='customer'","");
myQuery.SelectSQL();
with(myQuery)
{
while (!eof)
{
Response.Write(FieldValue("comp_name")+"
");
NextRecord();
}
}

Stored Procedures can return result sets and the QueryObject can be used to handle the results.

We can create a stored procedure called "getListOfOppos" like this

CREATE procedure getListOfOppos
@companyId int
AS
SELECT * FROM opportunity WHERE oppo_deleted is null and oppo_primarycompanyid = @companyId
GO

We can then use the QueryObject to run the procedure.

var strSQL = "execute getListOfOppos 28";
var myQuery = CRM.CreateQueryObj(strSQL,"");
myQuery.SelectSQL();
with(myQuery)
{
while (!eof)
{
Response.Write(FieldValue("oppo_opportunityid")+"
");
Response.Write(FieldValue("oppo_description")+"
");
Response.Write("

NextRecord();
}
}

An equivalent technique can be used in the .NET API.

Note:

It is not possible to use this technique directly in a List block e.g.

var myBlock = CRM.GetBlock("List");
myBlock.SelectSQL = "execute getListOfOppos 28";

This is because Sage CRM automatically changes the statement in the "SelectSql" property to provide the count and paging mechanism for the list block.