CREATE PROCEDURE ProductList
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT p.ProductID
		  ,p.ProductName   
		  ,p.CategoryID 
		  ,c.CategoryName  
	FROM Products p
	JOIN Categories c on p.CategoryID = c.CategoryID
END

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="DAL.Domain" assembly="DAL">
  <sql-query name="ProductList" callable="true">        
    EXEC ProductList
  </sql-query>
</hibernate-mapping>

using System;

namespace DAL.Domain
{
    public class ProductWithCategory
    {
        public virtual int ProductID { get; set; }
        public virtual string ProductName { get; set; }
        public virtual int CategoryID { get; set; }
        public virtual string CategoryName { get; set; }
    }
}

return session.GetNamedQuery("ProductList")
    .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ProductWithCategory)))
    .List<ProductWithCategory>();

public class ProductWithCategory
{
    public virtual int ProductID { get; set; }
    public virtual string ProductName { get; set; }
    public virtual int X { get; set; } // А запрос возвращает CategoryID !!! Что делать нах? :)
    public virtual string CategoryName { get; set; }
}

var query = session.CreateSQLQuery("EXEC ProductList");
var transformer = new ResultTransformer(typeof(ProductWithCategory), "ProductID", "ProductName", "X", "CategoryName");
var result = query.SetResultTransformer(transformer).List<ProductWithCategory>();

public class Category
{
    public static class Properties
    {
        public const string CategoryId = "CategoryId";
        public const string CategoryName = "CategoryName";
        public const string Products = "Products";
    }

    public Category()
    {
        Products = new List<Product>();
    }

    public virtual int CategoryId { get; set; }
    public virtual string CategoryName { get; set; }
    public virtual IList<Product> Products { get; protected set; }

    public virtual void AddProduct(params Product[] products)
    {
        foreach (var product in products)
        {
            Products.Add(product);
            product.Category = this;
        }
    }
}

ISQLQuery query = session.CreateSQLQuery(...); 

var users = session.CreateSQLQuery("SELECT UserName, Password FROM Users").List();