Re: Output Parameters in Stored Procedures

[Bob Hanson <mn...@gmail.com>]

Below I have pasted a reply from Gilles about how to use an insert stored
procedure.

I am currently having a problem trying to do this.

I tried to use an example similar to below and I'm receiving an error that
says "Cannot insert explicit value for identity column in table
'tbl_Recording_Packages' when IDENTITY_INSERT is set to OFF."

I assume this is because I'm using a ParameterMap like the one below that
specifies the Id column.

How do I specify in my ParameterMap that the stored procedure will return
the id of the newly inserted row?

- - - - - - - - - -
Re: Output Parameters in Stored Procedures

Gilles Bayon
Wed, 08 Feb 2006 12:00:04 -0800

You should use ExecuteInsert

/// <summary>
/// Execute an insert statement. Fill the parameter object with
/// the ouput parameters if any, also could return the insert generated key
/// </summary>
/// <param name="session"> The session</param>
/// <param name="parameterObject"> The parameter object used to fill the
statement.</param>
/// <returns>Can return the insert generated key.</returns>
object ExecuteInsert(IDalSession session, object parameterObject );

Here a sample from Nunit test
 Category category = new Category();
category.Name = "Mapping object relational";
sqlMap.Insert("InsertCategoryViaStoreProcedure" , category);
Assert.AreEqual(1, category.Id );
<
procedure id=" InsertCategoryViaStoreProcedure" parameterMap=" insert-params
">
ps_InsertCategorie
</ procedure>

<
parameterMap id=" insert-params">
<parameter property ="Id" column ="Category_Id" dbType ="Int" />
< parameter property=" Name" column="Category_Name "/>
<parameter property ="Guid" column= "Category_Guid" dbType ="
UniqueIdentifier"/>
</ parameterMap>it is for SQL Server but you will find the same example for
Oracle