While calling SQL Server stored procedure from NHibernate can I pass list or custom data type as a parameter

I tested this with Oracle. It is working fine. Because there I have Package and defined Associative Array type and Stored Procedure in Package body.

Though there are no concept of packages in SQL Server. How to make this work in SQL Server?

Domain Object Start

[Serializable]
public class Employee
{
    public virtual int EmployeeId
    {
        get;
        set;
    }
    public virtual string EmployeePassword
    {
        get;
        set;
    }

    public virtual string EmployeeName
    {
        get;
        set;
    }

    public virtual int TeamAssociatedWith
    {
        get;
        set;
    }
    public virtual string IsCaptain
    {
        get;
        set;
    }
    public virtual int NumberOfMOM
    {
        get;
        set;
    }
    public virtual int Balance
    {
        get;
        set;
    }       
}

Mapping

<?xml version="1.0" encoding="utf-8" ?>  
 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">  
   <class name="DomainObject.Employee,DomainObject" table="Employee">  
   <id name="EmployeeId" column="EMP_ID" type="int" unsaved-value="0">  
     <generator class="native">         
     </generator>  
   </id>  
   <property name="EmployeePassword" column="EMP_PASSWORD" type="string"/>  
   <property name="EmployeeName" column="EMP_NAME" type="string"/>    
   <property name="TeamAssociatedWith" column="TEAM_ASSOCIATED_WITH" type="int"/>  
   <property name="IsCaptain" column="IS_CAPTAIN" type="string"/>  
   <property name="Balance" column="BALANCE" type="int"/>  
   <property name="NumberOfMOM" column="NO_OF_MOM" type="int"/>  
 </class>  
</hibernate-mapping>  

Stored procedure

CREATE PROCEDURE [dbo].[some_sp] @id IntTable READONLY   
AS  
SELECT EMP_ID,EMP_NAME,EMP_PASSWORD,
TEAM_ASSOCIATED_WITH,IS_CAPTAIN,NO_OF_MOM,BA开发者_StackOverflowLANCE 
FROM employee; 
GO

ISQLQuery final = eventhistorysession.CreateSQLQuery("EXEC TestCustom @location = :id");

IQuery result = final.SetStructured("id", dt);

IList finalResult = result.List();

---

In SQL Server, stored procedures can have parameters of type table that can be used to mimic the Oracle Associative Array feature. In your situation, you'd be sending a "table" with a single row and multiple columns. There is a good example for NHibernate here in the accepted answer.