Pass List of Integers to Stored Procedure
Solution 1
rodmunera's answer has the correct general idea. Here is how I finally got it to work.
In sql server, I started with this:
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:
<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#">
Solution 2
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
<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.
Dan Bracuk
Updated on June 09, 2022Comments
-
Dan Bracuk almost 2 years
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:
exec dbo.Dan '1.2'
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>
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?