sql - 将列表传递给 SQL 自定义类型

Question

我想将 Coldfusion 10 的学年列表传递给 SQL Server 2008 R2 上的存储过程。我在 MSSQL 中创建了一个自定义类型:

CREATE TYPE YearListType AS Table (years VARCHAR(10))

然后我的存储过程在我的存储过程中声明:

CREATE PROCEDURE [getCounts]
    @years YearListType readonly
    ....
    SELECT .......
    WHERE school_year IN (SELECT * FROM @years)

现在在我的 Coldfusion 中，我这样调用存储过程:

<cfstoredproc procedure="[getCounts]" datasource="...">
    <cfprocparam cfsqltype="cf_sql_varchar" value="#yrlist#">
     ....
 </cfstoredproc>

变量 yrlist 是一个逗号分隔的列表。示例值如下所示:

"2001-2002,2002-2003,2003-2004"

当我执行时，我收到一个 CF 错误:

Operand type clash: varchar is incompatible with YearListType 

我理解错误，但我不知道如何传递列表。我尝试将 list="yes"添加到 cfprocparam，但我收到一条错误消息，指出 list 参数与 cfprocparam 不兼容。据我所知，没有 cf_sql_list 类型，是吗？

我应该如何将值列表传递给我的存储过程？我什至应该使用自定义 SQL 数据类型吗？

我已阅读 this和 this ，但我无法弄清楚它的 Coldfusion 方面。

Answer 1

我以前做过这个。您首先需要创建一个函数来拆分列表，然后像这样在 SQL 中调用它。 FTR:这个不是我创造的，我几年前通过谷歌找到的。如果我有原始源链接，我会引用这些链接。我确信谷歌仍然拥有它们。只需搜索“T-SQL fnSplit”。

SELECT blah FROM foo WHERE bar IN (fnSplit(@valueList, ','))

这是您需要创建的功能。

USE [DBNAME]
GO

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 09/06/2011 19:08:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnSplit](
        @sInputList VARCHAR(8000) -- List of delimited items
        , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
        ) RETURNS @List TABLE (item VARCHAR(8000))

    BEGIN
            DECLARE @sItem VARCHAR(8000)
            WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
            BEGIN
            SELECT
            @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
            @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

            IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
            END

            IF LEN(@sInputList) > 0
            INSERT INTO @List SELECT @sInputList -- Put the last item in
            RETURN
        END
GO