static void

NHibernate SQL

Using the NHibernate abstraction is generally best, but sometimes it's easier (or just more familiar) using raw SQL.

Using normal ADO

If you have a stored procedure or a particular sql you need to run, you don't have to stay in NHibernate. NHibernate's ISession exposes the IDbConnection. The only thing to check is enlisting in ambient transactions.

using (IDbCommand cmd = Session.Connection.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Ten Most Expensive Products";
    //IDbDataParameter parameter = cmd.CreateParameter();
    //parameter.ParameterName = "Product";
    //parameter.Value = "10";
    //cmd.Parameters.Add(parameter);
    if (Session.Transaction != null && Session.Transaction.IsActive)
        Session.Transaction.Enlist(cmd);
    using (IDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            string product = rdr[0].ToString();
            decimal cost = rdr.GetDecimal(1);
            //do sonething with them
        }
    }
}

ISession.CreateSQLQuery

The SqlQuery is an IQuery just like HQL (note the parameters have the ":" prefix).

                //products more expensive than average for category
                string sql = @"SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice >
    (SELECT AVG(UnitPrice) FROM Products
        WHERE CategoryID = :category)
    AND
     CategoryID = :category
ORDER BY Products.UnitPrice DESC";
                IQuery sqlQuery = session.CreateSQLQuery(sql)
                    .SetInt32("category", category.Id);
                var list = sqlQuery.List(); //or if simple scalar query.UniqueResult<int>();

You can also cast the result directly into one of your mapped entities with AddEntity(sadly not unmapped entities unless you can use a ResultTransformer -see below). All the sql output columns must be mapped to the entity properties (you can't miss any out- no lazy loaded properties).
Annotate the sql with {aliases in curly brackets}-
SELECT ProductID AS {p.Id}, ProductName AS {p.ProductName} ... FROM Products {p}.
{p.*} is a shortcut to mapping all properties (the sql contains the column names, not *).

    string sql = @"SELECT {p.*}
FROM Products {p}
WHERE UnitPrice >
    (SELECT AVG(UnitPrice) FROM Products
        WHERE CategoryID = :category)
    AND
     CategoryID = :category
ORDER BY {p.UnitPrice} DESC";
    IQuery query = session.CreateSQLQuery(sql)
                .AddEntity("p", typeof(Product))
        .SetEntity("category", category);
    var list2 = query.List<Product>();

Named Queries

These have the same rules as above, but are written into xml mapping files, and prepared and cached when the session factory is built.

var listOfProducts = session.GetNamedQuery("ProductsMoreExpensiveThanAverageByCategory")
    .SetEntity("category", category)
    .List<Product>();

The mapping file. The CDATA is optional but allows the > sign. Within the <return> you can add explicit mapping like this: <return-property column="ProductID" name="Id"/>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Northwind" assembly="Northwind">
  <sql-query name="ProductsMoreExpensiveThanAverageByCategory">
    <return class="Product" alias="p" />
    <![CDATA[
    SELECT {p.*}
    FROM Products {p}
    WHERE UnitPrice >
    (SELECT AVG(UnitPrice) FROM Products
    WHERE CategoryID = :category)
    AND
    CategoryID = :category
    ORDER BY {p.UnitPrice} DESC
    ]]>
  </sql-query>
</hibernate-mapping>

Stored Procedures

They can be called in the same way (named queries or CreateSqlQuery)

string sql = @"exec CustOrdersOrders :customerId";
IQuery query = session.CreateSQLQuery(sql)
    .SetString("customerId", "ALFKI");

If they return column names identical to a mapped entity you could use AddEntity (perhaps specifying prefixes). More likely you'll have to map it manually:

var list = query.List();
foreach (object[] item in list)
{
    var order = new Northwind.Order();
    order.Id = (int)item[0];
    order.OrderDate = (DateTime)item[1];
}

There is a built-in mapper for non-mapped entities, if your column names match the properties (case sensitive, and you can't leave any out).

query = query.SetResultTransformer(Transformers.AliasToBean<OrderDto>());
var list2 = query.List<OrderDto>();

With named queries, you must specify a mapped class, and the stored procedure must return all the properties (although you can remap the names with return-property).