Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I pass parameters to a stored procedure in NHibernate when one of the parameters is a list?

I have a stored proc mapped as follows in NHibernate:

 <sql-query name="HistoricSearch">
     <return class="ResultItem">
     </return>
     exec dbo.SelectHistoricResultItem :StartDate, :EndDate, :ListA, :ListB, :ListC
 </sql-query>

The following code works fine if each list of search options contains a single parameter:

 IQuery query = session.GetNamedQuery("HistoricSearch");
            query.SetDateTime("StartDate", fromDate);
            query.SetDateTime("EndDate", toDate);
            query.SetParameterList("ListA", searchOptionA);
            query.SetParameterList("ListB", searchOptionB);
            query.SetParameterList("ListC", searchOptionC);
            List<ResultItem> resultItems = (List<ResultItem>)query.List<ResultItem>();
            return resultItems;

but if a list contains multiple values, I get the following error:

 Procedure or function SelectHistoricResultItem has too many arguments specified

According to the description of the SetParameterList() method, it should "Bind multiple values to a named query parameter". I assume that the problem is in the mapping file at:

 exec dbo.SelectHistoricResultItem :StartDate, :EndDate, :ListA, :ListB, :ListC

but I have no idea how else to structure this to allow for multiple parameters. Can anyone tell me how this is done?

Thanks <3

like image 451
Franchesca Avatar asked Oct 11 '22 07:10

Franchesca


People also ask

How do you pass a list of values to a parameter of a stored procedure?

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'). query('.

Can we pass parameters to stored procedures?

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

What type of parameters you can provide to stored procedure?

Input parameters allow the caller to pass a data value to the stored procedure or function. Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.


1 Answers

Ok, after some experimentation I found that this method works for me.

            query.SetParameter("ListA", string.Join(", ", searchOptionA));
            query.SetParameter("ListB", string.Join(", ", searchOptionB));
            query.SetParameter("ListC", string.Join(", ", searchOptionC));
like image 188
Franchesca Avatar answered Oct 12 '22 21:10

Franchesca