Title: MS SQL Extended Stored Procedures Tutorial (Part 4)
Question: Using the Class TXpStoredProc form Part 3 we can now create a user friendly template that require minimal modification to create new XP's.
The trivial template take 3 integers as parameters and returns a data set with 3 row and 5 columns. The columns are basically each parameter multtiplied by 1,2,3,4,5 respectively. The Create of the class and the section that adds data rows are to things that need to be changed to create new XP's.
Open a query with SQL.Text = 'exec master..xp_TimesTable ''100'',''200'',''300''';
attach it to a datasource and a dbgrid and you will see a 3 by 5 data set.
NOTE : Remember you must have permission to execute the XP.
Next .. Now we have a simple template we will rewrite the DLL from Part 2 into an easier maintable unit.
Answer:
// =============================================================================
// LIBRARY XP_TEMPLATE
// MS SQL Extended Stored Procedure XP_TIMESTABLE
//
// Template for implementation of a Ms Sql Extended Stored procedure
// eg. exec xp_TimesTable '123','567','1001'
//
// Mike Heydon 2006
//
// Place compiled DLL in Microsoft SQL Server\80\Tools\Binn directory.
// or ..\90\.. if Sql 2005 or above
//
// SQL Commands to test procedure
//
// use master
// sp_addextendedproc 'xp_TimesTable','xp_Template.dll'
// exec master..xp_TimesTable '12','567','1001'
// sp_dropextendedproc 'xp_TimesTable'
// dbcc xp_Template(free)
//
// The above XP is trivial for template purposes.
// It takes 3 Parameters of integer (as strings) and returns
// 3 rows of 5 columns as follows ...
//
// -----------------------------------------
// | Time1 | Time2 | Time3 | Time4 | Time5 |
// -----------------------------------------
// | 12 | 24 | 36 | 48 | 60 |
// -----------------------------------------
// | 567 | 1134 | 1701 | 2268 | 2835 |
// -----------------------------------------
// | 1001 | 2002 | 3003 | 4004 | 5005 |
// -----------------------------------------
//
// =============================================================================
library xp_Template;
uses SysUtils,Classes,MsOdsApi,MahMsSqlXP;
{$R *.res}
// ==================================================================
// It is highly recommended that all Microsoft SQL Server 7.0
// and greater Extended Stored Procedure DLLs implement
// and export __GetXpVersion.
// ==================================================================
function _GetXpVersion : longword; cdecl;
begin
Result := ODS_VERSION;
end;
// ===================================================================
// Main MS SQL Function Entry Point
// ===================================================================
function xp_TimesTable(ASrvProc : SRV_PROC) : SRVRETCODE; cdecl;
var oXProc : TXpStoredProc;
iResult : SRVRETCODE;
i : integer;
begin
// Create the XP_StoredProc Class. We need ...
// 1) The pointer from the function argument (ASrvProc)
// 2) The name in ascii of the XP (same as FUNCTION name)
// 3) A string array of the parameter names. (Used to get ParamCount and
// to build up usage help string)
// eg. 'Usage : xp_TimesTable ,,'
// 4) A string array of the column titles of the output record
// set. This also denotes the number of columns to return.
oXProc := TXpStoredProc.Create(ASrvProc,'xp_TimesTable',
['Value1','Value2','Value3'],
['Time1','Time2','Time3','Time4','Time5']);
// Check if Parameters are ok. ie. Correct count and ALL type string.
// We could check property ParamCount=3 or Errors. We will use Errors.
if not oXProc.Errors then begin
// =========================================================================
// ** USER DEFINED CODE **
// This is where the user will build up the returned rows
for i := 0 to oXProc.ParamCount - 1 do begin
oXProc.AddResultRow([IntToStr(StrToIntDef(oXProc.Params[i],0)),
IntToStr(StrToIntDef(oXProc.Params[i],0) * 2),
IntToStr(StrToIntDef(oXProc.Params[i],0) * 3),
IntToStr(StrToIntDef(oXProc.Params[i],0) * 4),
IntToStr(StrToIntDef(oXProc.Params[i],0) * 5)
]);
end;
// ** END OF USER DEFINED **
// =========================================================================
// Finally generate the Results Data Set from the rows and return success
iresult := oXProc.CreateResultSet;
end
else
iResult := XP_ERROR;
FreeAndNil(oXProc);
Result := iResult;
end;
// =============================================================================
// Export declarations
// =============================================================================
exports xp_TimesTable,
_GetXpVersion;
begin
end.