title: Execute stored procedure with NHibernate
body: <p>I have stored procedure named "GetListingId" in my database. It takes three string input parameters: <code>City</code>, <code>State</code>, <code>PhoneNumber</code>; one input long parameter: <code>CallId</code> and return one output long parameter: <code>ListingId</code>.</p>
  
  <p>I'm trying execute it in code:</p>
  
  <pre><code>IQuery query = this.appListingRepository.Session.CreateSQLQuery("exec GetListingId @City=:city, @State=:state, @PhoneNumber=:phoneNumber, @CallId=:callId")
                  .SetString("city", "SAN JOSE")
                  .SetString("state", "CA")
                  .SetString("phoneNumber", "+14084801753")
                  .SetInt64("callId", 1);
  
          long id = query.UniqueResult&lt;long&gt;();
  </code></pre>
  
  <p>But this throw exeption: <code>Invalid column name 'ListingId'</code></p>
  
  <p>Stored procedure in SQL Server Managment Studio executed fine.</p>
  
  <p>Stored procedure code:</p>
  
  <pre><code>USE [Clickmob_ReportingDb_test]
  GO
  /****** Object:  StoredProcedure [dbo].[GetListingId]    Script Date: 11/24/2012 10:46:40 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  --CREATE PROCEDURE GetListingId
  ALTER PROCEDURE [dbo].[GetListingId]
  (
      @City  varchar(255),
      @State varchar(255),
      @PhoneNumber varchar(255),
      @CallId bigint
  )
  AS
  BEGIN
    DECLARE @TotalPrice float;
    DECLARE @TotalCount float;
    DECLARE @DestNumber varchar(255);
    DECLARE @IsCustD    int;
    DECLARE @Result bigint;
  
  -- Check Duplicated Calls for Listings --------------------------------------------------------------------------
  
    IF EXISTS (SELECT t.name
               FROM   tempdb.sys.tables t
               WHERE  t.name = '##tmp_acc')
      DROP TABLE ##tmp_acc;
  
    SELECT Li.AppListingId, Li.ListingInfoId INTO ##tmp_acc
    FROM   CALLS c, USERDATAS ud, LISTINGINFOS Li
    WHERE  c.ReportingUserDataId = ud.UserDataId
    AND    Li.CallId = c.CallId
    AND    c.DateTime &gt; DateAdd(mm,-3,getdate())
    AND    Li.DestinationPhoneNumber = (SELECT TOP 1 ud.PhoneNumber
                                        FROM   CALLS c, USERDATAS ud
                                        WHERE  c.ReportingUserDataId = ud.UserDataId
                                        AND    c.CallId = @CallId);
    SELECT @IsCustD = COUNT(*) FROM ##tmp_acc;
  
  -----------------------------------------------------------------------------------------------------------------
  -- Check Quota Limits -------------------------------------------------------------------------------------------
  
    IF EXISTS (SELECT t.name
               FROM   tempdb.sys.tables t
               WHERE  t.name = '##tmp_quots')
      DROP TABLE ##tmp_quots;
  
    SELECT L.ListingId INTO ##tmp_quots
    FROM  (SELECT L.ListingId, L.MonthlyQuotaLimit, L.MonthlyBudget, L.DailyQuotaLimit, L.DailyBudget,
                  L.SortingPrice, L.Status, acl.CityId
           FROM   AppLISTINGS L, AppCityToListing acL
           WHERE  L.ListingId=acL.ListingId) AS L
    LEFT JOIN
          (SELECT m.CityId, m.CampaignId, m.AppSubCategoryId, m.AppListingId, m.MonthlyCallEarning, m.MonthlyCallCount,
                  ISNULL(d.DailyCallEarning, 0) AS DailyCallEarning, ISNULL(d.DailyCallCount, 0) AS DailyCallCount
           FROM  (SELECT c.CityId, c.CampaignId, Li.AppSubCategoryId, Li.AppListingId,
                         SUM(Li.CallEarning) AS MonthlyCallEarning, COUNT(c.CallId) AS MonthlyCallCount
                  FROM   CALLS c, LISTINGINFOS Li
                  WHERE  Li.CallId=c.CallId
                  AND    Li.CallStatus=1
                  AND    YEAR(c.DateTime)=YEAR(getdate())
                  AND    MONTH(c.DateTime)=MONTH(getdate())
                  GROUP BY c.CityId, c.CampaignId, Li.AppSubCategoryId, Li.AppListingId) AS m
           LEFT JOIN
                 (SELECT c.CityId, c.CampaignId, Li.AppSubCategoryId, Li.AppListingId,
                         SUM(Li.CallEarning) AS DailyCallEarning, COUNT(c.CallId) AS DailyCallCount
                  FROM   CALLS c, LISTINGINFOS Li
                  WHERE  Li.CallId=c.CallId
                  AND    Li.CallStatus=1
                  AND    YEAR(c.DateTime)=YEAR(getdate())
                  AND    MONTH(c.DateTime)=MONTH(getdate())
                  AND    DAY(c.DateTime)=DAY(getdate())
                  GROUP BY c.CityId, c.CampaignId, Li.AppSubCategoryId, Li.AppListingId) AS d
           ON (m.CityId=d.CityId AND m.CampaignId=d.CampaignId AND m.AppSubCategoryId=d.AppSubCategoryId AND m.AppListingId=d.AppListingId)) AS s
    ON (L.ListingId=s.AppListingId AND L.CityId=s.CityId)
    WHERE L.MonthlyQuotaLimit&lt;=s.MonthlyCallCount
    OR    L.MonthlyBudget&lt;=s.MonthlyCallEarning
    OR    L.DailyQuotaLimit&lt;=s.DailyCallCount
    OR    L.DailyBudget&lt;=s.DailyCallEarning;
  
  -----------------------------------------------------------------------------------------------------------------
  -- Accessible Listings ------------------------------------------------------------------------------------------
  
    IF EXISTS (SELECT t.name
               FROM   tempdb.sys.tables t
               WHERE  t.name = '##tmp_tbl')
      DROP TABLE ##tmp_tbl;
  
    SELECT DISTINCT SL.ListingId INTO ##tmp_tbl
    FROM  (SELECT act.CityId
           FROM   AppCities act, AppStates ast
           WHERE  act.StateId=ast.StateId
           AND    UPPER(act.Name)=UPPER(@City)
           AND    UPPER(ast.Name)=UPPER(@State)) AS SC,
  
          (SELECT aL.ListingId, aLm.MediaId AS ListingMediaId, abm.BrandId, abm.MediaId AS BrandMediaId,
                  acm.CustomerId, acm.MediaId AS CustomerMediaId, ascL.SubCategoryId, acL.CityId
           FROM   AppLISTINGS aL, AppCityToListing acL, AppListingToMedia aLm, AppBRANDS ab, AppBrandToMedia abm,
                  AppCustomerToMedia acm, AppSubCategoryToListing ascL
           WHERE  aL.ListingId=aLm.ListingId
           AND    aL.ListingId=acL.ListingId
           AND    aL.BrandId=ab.BrandId
           AND    ab.BrandId=abm.BrandId
           AND    ab.CustomerId=acm.CustomerId
           AND    aL.ListingId=ascL.ListingId) AS SL,
  
          (SELECT m.Number, m.CampaignId, m.MediaId, m.SubCategoryId,
                  ISNULL(abc.BrandId, 0) AS ExceptBrandId,
                  ISNULL(aLc.ListingId, 0) AS ExceptListingId,
                  ISNULL(acc.CustomerId, 0) AS ExceptCustomerId
           FROM  (SELECT ap.Number, ac.CampaignId, ach.MediaId, ascc.SubCategoryId
                  FROM   AppPHONENUMBERS ap, AppCAMPAIGNS ac, AppCHANNELS ach, AppSubCategoryToCampaign ascc
                  WHERE  ap.CampaignId=ac.CampaignId
                  AND    ac.ChannelId=ach.ChannelId
                  AND    ap.CampaignId=ascc.CampaignId) AS m
           LEFT JOIN AppBrandToCampaign abc
           ON (m.CampaignId=abc.CampaignId)
           LEFT JOIN AppListingToCampaign aLc
           ON (m.CampaignId=aLc.CampaignId)
           LEFT JOIN AppCustomerToCampaign acc
           ON (m.CampaignId=acc.CampaignId)
           WHERE m.Number=@PhoneNumber) AS SM
  
    WHERE  SC.CityId=SL.CityId
    AND    SL.SubCategoryId=SM.SubCategoryId
    AND   (SL.ListingMediaId=SM.MediaId
    OR     SL.BrandMediaId=SM.MediaId
    OR     SL.CustomerMediaId=SM.MediaId)
    AND    SL.ListingId &lt;&gt;SM.ExceptListingId
    AND    SL.BrandId &lt;&gt;SM.ExceptBrandId
    AND    SL.CustomerId&lt;&gt;SM.ExceptCustomerId
    AND    SL.ListingId NOT IN (SELECT AppListingId
                                FROM   ##tmp_acc)
    AND    SL.ListingId NOT IN (SELECT ListingId
                                FROM   ##tmp_quots);
  
    SELECT @Result = COUNT(*) FROM ##tmp_tbl;
  -----------------------------------------------------------------------------------------------------------------
    IF @Result &gt; 0
      BEGIN
        SET @IsCustD = 0;
  
        SELECT @TotalPrice = SUM(sel.Price)
                             FROM (SELECT aL.ListingId, CASE aL.SortingPrice WHEN 0 THEN aL.LeadPrice
                                                        ELSE aL.SortingPrice END AS Price
                                   FROM   AppListings aL) AS sel, ##tmp_tbl t
                             WHERE sel.ListingId=t.ListingId;
  
        SELECT @TotalCount = SUM(aL.CallCount)
                             FROM  AppListings aL, ##tmp_tbl t
                             WHERE aL.ListingId=t.ListingId;
  
        SELECT @Result = (SELECT TOP 1 ISNULL(sel.ListingId, 0) AS ListingId
                          FROM  (SELECT L.ListingId, (L.Price/@TotalPrice - L.CallCount/(@TotalCount+1)) AS Rate
                                 FROM  (SELECT aL.ListingId, aL.CallCount, CASE aL.SortingPrice WHEN 0 THEN aL.LeadPrice
                                                                           ELSE aL.SortingPrice END AS Price
                                        FROM   AppListings aL) AS L) AS sel, ##tmp_tbl t
                          WHERE  sel.ListingId=t.ListingId
                          ORDER BY sel.Rate DESC);
      END
    ELSE
      BEGIN
        IF @IsCustD &gt; 0             
          BEGIN
            SET @IsCustD = 1;
  
            SELECT @TotalPrice = SUM(sel.Price)
                                 FROM (SELECT aL.ListingId, CASE aL.SortingPrice WHEN 0 THEN aL.LeadPrice
                                                            ELSE aL.SortingPrice END AS Price
                                       FROM   AppListings aL) AS sel, ##tmp_acc t
                                 WHERE sel.ListingId=t.ListingId;
  
            SELECT @TotalCount = SUM(aL.CallCount)
                                 FROM  AppListings aL, ##tmp_acc t
                                 WHERE aL.ListingId=t.ListingId;
  
            SELECT @Result = (SELECT TOP 1 ISNULL(sel.ListingId, 0) AS ListingId
                              FROM  (SELECT L.ListingId, (L.Price/@TotalPrice - L.CallCount/(@TotalCount+1)) AS Rate
                                     FROM  (SELECT aL.ListingId, aL.CallCount, CASE aL.SortingPrice WHEN 0 THEN aL.LeadPrice
                                                                               ELSE aL.SortingPrice END AS Price
                                            FROM   AppListings aL) AS L) AS sel, ##tmp_acc t
                              WHERE  sel.ListingId=t.ListingId
                              ORDER BY sel.Rate DESC);
          END
        ELSE
          RETURN 0;        
      END;            
  
    UPDATE AppLISTINGS
    SET    CallCount=CallCount+1
    WHERE  ListingId=@Result;
  
    SELECT @DestNumber = (SELECT TOP 1 ISNULL(n.Number, '') AS Number
                          FROM   AppPHONENUMBERS n
                          WHERE  n.PhoneCallListingId=@Result
                          ORDER BY n.PhoneNumberId);
  
    INSERT INTO LISTINGINFOS (CallStatus, IsCustomerDuplicate, DestinationPhoneNumber, CallId, AppListingId)
    VALUES (5, @IsCustD, @DestNumber, @CallId, @Result);
  
    RETURN @Result;
  END
  </code></pre>