Product Documentation

FairCom C# Stored Procedures

Previous Topic

Next Topic

Writing the Code

The FairCom Visual Studio extension created a stored procedure skeleton for you. By default, the document created is shown with generated code automatically collapsed. Usually, you should not need to alter this code; however, for advanced customization, contact FairCom for additional customization details.

You write actual stored procedure code by implementing the ExecuteSP methods (see SqlStoredProc, SqlUdf, or SqlTrigger in The .NET Ctree.SqlSP Assembly). In writing your code, you can add new methods if necessary.

Note: You should not change the prototype of the ExecuteSP methods, which reflect actual stored procedure arguments.

Returning Errors

A stored procedure throws an exception to generate an error sent to the client. An end user always sees a FairCom DB SQL error ‑20142 returned in this case. The error message indicates an error in executing the stored procedure followed by the error number and an associated message, such as:

error(-20142): Error in Stored Procedure/function Execution - error(-34567): my error message.

The API used to write stored procedures and the .NET runtime may also throw exceptions. If these are not caught by your exception handling, they are passed to the FairCom DB SQL runtime and result in a ‑20142 error.

Null Handling

For easier null value handling, SQL types are mapped into nullable .NET types. Whenever a “SQL value” is referenced, the type used is nullable. Whenever an object representing a SQL value is set to null, it indicates a “SQL value” was null (reading a column/parameter value) or it is set to null when updating (setting a column/parameter value).