rough example as it's nearly lunchtime

nHibernate version

==================

(this is roughly what it comes out as - made slightly easier for reading)

DECLARE @p0 INT --etc...for each param

SET @p0 = 1234 --and so on

SELECT * FROM Blah WHERE (this_.ID IN(@p0 , @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8)

Better version

==============

DECLARE @BinaryIdListInput VarBinary(8000)

SET @BinaryIdListInput = '1234, 5678' --etc...

CREATE PROCEDURE GetMyList (@BinaryIdList varbinary(8000))

AS

DECLARE @IdList TABLE (Id INT PRIMARY KEY CLUSTERED)

INSERT INTO @IdList(Id) SELECT PIA.Value FROM ParseIntegerArray(@BinaryIdList, 4) AS PIA

SELECT * FROM Blah WHERE (this_.ID IN(SELECT Id From @IdList))

I'd then call a stored proc using EXEC GetMyList @BinaryIdList = @BinaryIdListInput

There is a more performant way of doing this with joining on the table variable, but the above gives you the closest comparable. The single version of the execution plan comes from passing in the varbinary in this case (I've set it above to simplify things a touch for reading)

I do use a function to parse the array and this is as follows :

CREATE FUNCTION [ParseIntegerArray]

(

@BinaryIMAGE

,@ElementSizeTINYINT

)

RETURNS @Array TABLE

(

[Index] SMALLINT IDENTITY(0,1)

,[Value] VARBINARY(8) NOT NULL

)

AS

BEGIN

IF ( @ElementSize IS NOT NULL AND @ElementSize IN ( 1, 2, 4, 8 ) )

BEGIN

DECLARE @Value VARBINARY(8)

,@Length SMALLINT

,@Index SMALLINT;

-- initialize variables

SET @index = 1;

SET @Length = DATALENGTH( @Binary );

-- extract values

WHILE ( @index <= @Length )

BEGIN

-- get value

SET @Value = SUBSTRING( @Binary, @index, @ElementSize );

-- insert into table

INSERT INTO @Array VALUES( @Value );

-- increment index

SET @index = @index + @ElementSize;

END;

END;

RETURN;

END

GO