sql - NHibernate calling procedure using named query in c# -

i facing problem while calling procedure using named query in nhibernate. can call simple procedure / parametrized procedure have procedure accept 3 argument, 1 varchar(max) type have guid seperated commas(this passed in parameter sql query), other varchar(50) , last 1 bit type.

i have following procedure

create procedure [dbo].[ar_tarcustomer_readaccount_forbalancedetail]       @customerlist varchar(max),      @asofdate  varchar(50),      @postedonly  bit             set nocount on;      declare @errornumber int     declare @sql   varchar(max)     declare @postedinvoice varchar(50)     declare @postedpayment varchar(200)      set @postedinvoice = ''     set @postedpayment=  ''     if (@postedonly = 1)     begin      set @postedinvoice = ' , ari.fisposted = 1 '      set @postedpayment = ' , pay.fisposted =         case pay.fpaymenttype          when ''cm'' 0          when ''epd'' 0          else 1        end'     end      set @sql =      '     select *     (      select distinct        co.fname ftrxcompanyname, prop.fname ftrxpropertyname, arc.fname ftrxcustomername,           cast(ari.farinvoicenumber  varchar(10)) ftrxnumber,        ari.finvoicedescription ftrxdescription,        ari.fisvoided ftrxisvoided,        case finvoicetype          when ''credit memo'' fbalance          when ''early payment discount'' fbalance          else famount        end ftrxamount      tarinvoice ari       inner join tarcustomer arc on arc.fcustomerid = ari.fcustomerid       left outer join tsccompany co on co.fcompanyid = arc.fcompanyid       left outer join tscproperty prop on prop.fpropertyid = arc.fpropertyid       ari.fcustomerid in (' + @customerlist + ') ,         ari.finvoicedate >= convert(datetime,'+ '''' + @asofdate + '''' + ') ,         ari.finvoicetype <> ''recurring template''' + @postedinvoice + '       union       select  distinct        co.fname ftrxcompanyname, prop.fname ftrxpropertyname, cust.fname ftrxcustomername,             ''payment'' ftrxtype,         null,             pay.fcomments ftrxdescription,        pay.fisvoided ftrxisvoided,                 pay.famount ftrxamount      tarpayment pay       left outer join tarpaymentinvoice payinv on pay.fpaymentid = payinv.fpaymentid       left outer join tarinvoice inv on payinv.finvoiceid = inv.farinvoiceid       left outer join tarcustomer cust on cust.fcustomerid = pay.fcustomerid       left outer join tsccompany co on co.fcompanyid = cust.fcompanyid       left outer join tscproperty prop on prop.fpropertyid = cust.fpropertyid        (inv.fcustomerid in (' + @customerlist +')       or (pay.fcustomerid in (' + @customerlist +') , (inv.fcustomerid not in (' + @customerlist +') or inv.fcustomerid null))       or ((pay.fcustomerid null or pay.fcustomerid not in (' + @customerlist +')) , inv.fcustomerid in (' + @customerlist +')))       , pay.feffectivedate >= convert(datetime,' + '''' + @asofdate + '''' + ')'        + @postedpayment + '       union      -- voided payments reversed payment amount , voided description      select  distinct        co.fname ftrxcompanyname, prop.fname ftrxpropertyname, cust.fname ftrxcustomername,           ''payment'' ftrxtype,         null,        pay.fvoideddate ftrxaddeddate,        pay.funappliedamount ftrxbalance,        cast(pay.fchecknumber varchar(10)) ftrxnumber,        ''voided payment discount #'' + cast(pay.fchecknumber varchar(10)) ftrxdescription,        pay.fisvoided ftrxisvoided,                 (-1 * pay.famount) ftrxamount      tarpayment pay       left outer join tarpaymentinvoice payinv on pay.fpaymentid = payinv.fpaymentid       left outer join tarinvoice inv on payinv.finvoiceid = inv.farinvoiceid       left outer join tarcustomer cust on cust.fcustomerid = pay.fcustomerid       left outer join tsccompany co on co.fcompanyid = cust.fcompanyid       left outer join tscproperty prop on prop.fpropertyid = cust.fpropertyid        (inv.fcustomerid in (' + @customerlist +')       or (pay.fcustomerid in (' + @customerlist +') , (inv.fcustomerid not in (' + @customerlist +') or inv.fcustomerid null))       or ((pay.fcustomerid null or pay.fcustomerid not in (' + @customerlist +')) , inv.fcustomerid in (' + @customerlist +')))       , pay.fisvoided = 1       , pay.fvoideddate >= convert(datetime,' + '''' + @asofdate + '''' + ')'         + @postedpayment + '     ) x     ftrxamount not null     order ftrxcompanyname, ftrxpropertyname, ftrxaddeddate     '     exec (@sql)     --print (@sql)      return (@@error)  

i had written following code hbm file.

<sql-query name="ar_tarcustomer_readaccount_forbalancedetail_new" callable="true">     <!--<query-param name="customerlist" type="varchar(max)" />     <query-param name="asofdate" type="string" />     <query-param name="postedonly" type="bool" />-->     <return class="tarcustomer">       <return-property column="ftrxcompanyname" name="ftrxcompanyname" />       <return-property column="ftrxpropertyname" name="ftrxpropertyname" />       <return-property column="ftrxcustomername" name="ftrxcustomername" />       <return-property column="fcustomerid" name="ftrxcustomerid" />       <return-property column="ftrxsourceid" name="ftrxsourceid" />       <return-property column="finvoiceposted" name="finvoiceposted" />       <return-property column="ftrxdate" name="ftrxdate" />       <return-property column="fposteddate" name="fposteddate" />       <return-property column="ftrxtype" name="ftrxtype" />       <return-property column="ftrxduedate" name="ftrxduedate" />       <return-property column="ftrxaddeddate" name="ftrxaddeddate" />       <return-property column="ftrxbalance" name="ftrxbalance" />       <return-property column="ftrxdescription" name="ftrxdescription" />       <return-property column="ftrxisvoided" name="ftrxisvoided" />       <return-property column="ftrxamount" name="ftrxamount" />     </return>      exec ar_tarcustomer_readaccount_forbalancedetail_new     @customerlist=:customerlist,     @asofdate=:asofdate,     @postedonly=:postedonly   </sql-query> 

and calling procedure had written following code.

var customerbalance = session.getnamedquery("ar_tarcustomer_readaccount_forbalancedetail_new")                                            .setparameter("customerlist", "'''bced443a-ce86-4675-bca6-ae5646ad9c2e'' , ''bced443a-ce86-4675-bca6-ae5646ad9c2e'''")                                            .setparameter("asofdate", "10/1/2012")                                            .setparameter("postedonly", postedonly)                                            .setresulttransformer(new aliastobeanresulttransformer(typeof(tarcustomer))).list<tarcustomer>(); 

here had tried type of combination passing parameter procedure using nhibernate crased.

please me solve out problem stuck in problem last 3 days , it's urgent solve it.

i using c# language , nhibernate.

thanks,

try embedding exec of sql-query inside begin/end block:

begin     exec ar_tarcustomer_readaccount_forbalancedetail_new .. params .. end 

at least that's how got work in nh 3.2 mapping.bycode.