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
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('.
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.
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.
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With