将整数列表传递给存储过程

Here is my stored procedure:

这是我的存储过程:

ALTER PROCEDURE [dbo].[Dan] 
@numbers varchar(10)    
AS
BEGIN
SET NOCOUNT ON;
select @numbers numbers
END

In SSMS, I can execute it successfully like this:

在SSMS中,我可以像这样成功执行它:

exec dbo.Dan '1.2'

In ColdFusion, I can execute it successfully with either of these two sets of commands:

在ColdFusion中,我可以使用以下两组命令之一成功执行它:

<cfstoredproc procedure="dbo.dan" datasource="ClinicalDataDev">
<cfprocparam cfsqltype="cf_sql_varchar" value="1,2"> 
<cfprocresult name="abc">
</cfstoredproc>

<cfquery name="abcd" datasource="ClinicalDataDev">
exec dbo.Dan <cfqueryparam cfsqltype="cf_sql_varchar" value='1,2' list="no">
</cfquery>

However, I'm looking to improve on this by specifying the value as a list of integers. Since cfprocparam does not have a list attribute, I think I am restricted to the cfquery approach. My efforts and results so far are:

但是,我希望通过将值指定为整数列表来改进这一点。由于cfprocparam没有list属性,我认为我仅限于cfquery方法。到目前为止,我的努力和成果是:

<cfqueryparam cfsqltype="cf_sql_integer" value='1' list="no">
executes successfully.  The purpose is to see if the procedure accepts an 
integer - it does.

<cfqueryparam cfsqltype="cf_sql_integer" value='1,2' list="no">
also executes sucessfully, but returns a value of 42006.  Probably not 
worth persuing.

<cfqueryparam cfsqltype="cf_sql_integer" value='1,2' list="yes">
throws an error for two many paramters.  
The same thing happens with cf_sql_varchar.

As stated earlier, I can pass the list as a string, but that seems like a bit of a hack. Is there a way to pass the list of integers as a list of integers?

如前所述,我可以将列表作为字符串传递,但这似乎有点像黑客。有没有办法将整数列表作为整数列表传递?

2 个解决方案

#1

As other commenters mentioned before, passing table-valued parameters is the way to go. This will require you to change the input in the SP to take a table input and in your query you can do

正如之前提到的其他评论者所说,传递表值参数是可行的方法。这将要求您更改SP中的输入以获取表输入,并且您可以在查询中执行此操作

<cfquery>
    CREATE TABLE @temp (usedID int)
    INSERT INTO @temp 
        (usedID)
        VALUES
        (1)
        ,(2)

    exec dbo.Dan @temp
</cfquery>

You may have to change the way you pass your parameters to the SP, but this is the general idea.

您可能必须更改将参数传递给SP的方式,但这是一般的想法。

#2

rodmunera's answer has the correct general idea. Here is how I finally got it to work.

rodmunera的答案有正确的总体思路。这是我最终如何使用它。

In sql server, I started with this:

在sql server中,我从这开始:

CREATE  TYPE pt.IntegerTableType AS TABLE 
( integerIN int);
grant execute on type::pt.IntegerTableType to theAppropriateRole

Then I changed by stored proc to this:

然后我通过存储过程更改为:

ALTER PROCEDURE [dbo].[Dan] 
@numbers pt.IntegerTableType readonly
AS
BEGIN
SET NOCOUNT ON;
select 1 record 
where 1 in (select integerIN from @numbers) 
END

The Coldfusion code is this:

Coldfusion代码是这样的:

<cfset numbers = "1,2">
<cfquery name="abcd" datasource="ClinicalDataDev">
declare @dan as pt.IntegerTableType

insert into @dan
select null 
where 1 = 2
<cfloop list="#numbers#" index="number">
union
select <cfqueryparam cfsqltype="cf_sql_integer" value="#number#">
</cfloop>

exec dbo.Dan @dan
</cfquery>
<cfdump var="#abcd#">

#1