将存储过程的整数传递列表 [英] Pass List of Integers to Stored Procedure
问题描述
这是我的存储过程:
ALTER PROCEDURE [dbo].[Dan]
@numbers varchar(10)
AS
BEGIN
SET NOCOUNT ON;
select @numbers numbers
END
在SSMS中,我可以成功执行像这样:
In SSMS, I can execute it successfully like this:
exec dbo.Dan '1.2'
在ColdFusion中,我可以使用以下两组命令之一成功执行它:
In ColdFusion, I can execute it successfully with either of these two sets of commands:
<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>
但是,我希望通过将值指定为整数列表来改进。由于cfprocparam没有列表属性,我认为我仅限于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:
<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?
推荐答案
rodmunera的答案有正确的一般想法。
rodmunera's answer has the correct general idea. Here is how I finally got it to work.
在sql server中,我开始使用:
In sql server, I started with this:
CREATE TYPE pt.IntegerTableType AS TABLE
( integerIN int);
grant execute on type::pt.IntegerTableType to theAppropriateRole
到此:
ALTER PROCEDURE [dbo].[Dan]
@numbers pt.IntegerTableType readonly
AS
BEGIN
SET NOCOUNT ON;
select 1 record
where 1 in (select integerIN from @numbers)
END
Coldfusion代码是这样:
The Coldfusion code is this:
<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#">
这篇关于将存储过程的整数传递列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!