Search code examples
sql-servercodefluent

Array parameter in CFQL / stored procedure method

Is it possible to pass an array of parameter to a stored procedure generated through CFQL?

We generated method from designer without any problem. But I haven't found any way to unit execute it on SQL Server.

When trying to execute through code, the code would fail with given error:

Operand type clash: table type is incompatible with int

on the generated line: 

System.Data.IDataReader reader = CodeFluentContext.Get(XYZ.Constants.XYZCodeFluentStoreName).Persistence.ExecuteReader();

CFQL Body is as follow: 

LOAD (enumABCGroup[] groups) WHERE ABC.Group IN (@groups) ORDER BY NumberExt

Generated procedure is: 

CREATE PROCEDURE [XYZ].[ABC_LoadByGroups]
(
   @groups [int],
   @_orderBy0 [nvarchar] (64) = NULL,
   @_orderByDirection0 [bit] = 0
)
AS
    SET NOCOUNT ON
    SELECT DISTINCT ... 
    FROM [XYZ].[ABC]
    WHERE [XYZ].[ABC].[ABC_Group] IN (@groups)
    ORDER BY [XYZ].[ABC].[ABC_Group] ASC, [XYZ].[ABC].[ABC_Label] ASC

    RETURN
GO

Underlying database system is SQL Server 2012 (v11.0.5058.0).

Thanks for your help;

Solution

  • I think you forgot to configure the SQL Server producer to target at least SQL Server 2008:

    or in XML:

    <cf:producer name="SQL Server" typeName="CodeFluent.Producers.SqlServer.SqlServerProducer, CodeFluent.Producers.SqlServer">
  <cf:configuration targetVersion="Sql2008" />
</cf:producer>

    Here's some useful resources about CodeFluent Entities and TVP: