Sending List to Stored Procedure in CF

In CF we can acess stored procedures through <cfstoredproc> and we give param values in <cfprocparam> tags. We get some situations of passing list in <cfprocparam> as we don't have 'list' attribute in it. For sending list and acessing list in Stored procedures we have to use functions of SQL which is quite complex and time consuming.

We can make work around to achieve this using CF and Dynamic query in Stored procedures.
For passing Integer list value:
In CFM side

   <cfset VARIABLES.listValue = "1,2,3,4,5,6,7,8,9,10">  
 <cfstoredproc procedure="getClients" datasource="practise" >   
 <cfprocparam variable="ids" cfsqltype="cf_sql_VARCHAR" value=#VARIABLES.listValue# > <cfprocresult name="VARIABLES.getClients"></cfstoredproc>  
 <cfdump var="#VARIABLES.getClients#" > 
  

In SQL side

CREATE PROCEDURE [dbo].[getClients]  
 @ids varchar(255)  
 AS  
 BEGIN  
 declare @sql varchar(max)  
   set @sql = 'select * FROM Clients where id IN ('+@ids+')'  
   EXEC(@sql)  
 END  

For passing String list value:

In CFM side

 <cfset VARIABLES.listValue = 'ram,krishna,ramki'>  
 <cfset VARIABLES.listValue = replace(VARIABLES.listValue, ",", "','", 'all')>  
 <cfstoredproc procedure="getClients" datasource="practise">  
  <cfprocparam variable="ids" cfsqltype="cf_sql_VARCHAR" value=#VARIABLES.listValue#>  
  <cfprocresult name="VARIABLES.getClients">  
 </cfstoredproc>  
 <cfdump var="#VARIABLES.getClients#">  

In SQL side

 CREATE PROCEDURE [dbo].[getClients]  
 -- Add the parameters for the stored procedure here  
 @names varchar(255)  
 AS  
 BEGIN  
 declare @sql varchar(max)  
   set @sql = 'select * FROM Clients where companyName IN ('''+ @names +''')'  
 EXEC(@sql)  
 END