Creating and Editing Stored Procedures
To create a new Stored Procedure, right-click the Procedures node in the Databases tree of the Project Explorer and choose Create. To modify an existing stored procedure select the Stored Procedure in the Databases Tree in the Project Explorer by double-clicking on it or selecting Modify from the context menu . By convertion we recommend that you name your Stored Procedures beginning with "sp_xxx_", user-defined functions with "f_xxx_", and Triggers with "dt_xxx_", where xxx is the name of the table that they are associated with.
Testing the Procedure
To test run the Stored Procedure, select the Stored Procedure in the Databases Tree in the Project Explorer by double-clicking on it. Once the Database Administrator is displayed, click the Run button to run the procedure.
Getting return values
Example Stored Procedure called "sp_myproc":
| |
parameter arg1, arg2
return arg1 + arg2 |
Example calling the Stored Procedure from C# .NET:
| |
////////////////////////////////////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;
////////////////////////////////////////////////////////////////////////
// sample code to call a Stored Procedure that adds to numeric values together
public int CallStoredProcedure()
{
RecitalConnection conn = new RecitalConnection("Data Source=localhost;Database=southwind;uid=?;pwd=?");
RecitalCommand cmd = new RecitalCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_myproc(@arg1, @arg2)";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters["@arg1"].Value = 10;
cmd.Parameters["@arg2"].Value = 20;
conn.Open();
cmd.ExecuteNonQuery();
int result = (int)(cmd.Parameters["retvalue"].Value); // get the return value from the sp
conn.Close();
return result;
}
|
Writing Stored Procedures that return a Resultset
If you want to write a Stored Procedure that returns a ResultSet, you use the SETRESULTSET() function of the 4GL. Using the Universal .NET Data Provider, you can then execute the 4GL Stored Procedure and return the ResultSet to the client application for processing. ResultSets that are returned from Stored Procedures are read-only.
Example Stored Procedure called "sp_myproc":
| |
parameter query
select * from customers &query into cursor "mydata"
return setresultset("mydata") |
Example calling the Stored Procedure from C# .NET:
| |
////////////////////////////////////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;
////////////////////////////////////////////////////////////////////////
// sample code to call a stored procedure that returns a ResultSet
public void CallStoredProcedure()
{
RecitalConnection conn = new RecitalConnection("Data Source=localhost;Database=southwind;uid=?;pwd=?");
RecitalCommand cmd = new RecitalCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_myproc(@query)";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters["@query"].Value = "where not deleted()";
conn.Open();
RecitalDataReader dreader = cmd.ExecuteReader();
int sqlcnt = (int)(cmd.Parameters["sqlcnt"].Value); // returns number of affected rows
while (dreader.Read())
{
// read and process the data
}
dreader.Close();
conn.Close();
}
|
|
