Passing valuelist to stored procedure as integer list

[asiavoices]

Hi all,

I was wondering how I could convert and pass the values in a valuelist as a parameter to my stored procedure.

For example if wanted to do this using simply CFQuery:

SELECT * FROM Employees WHERE EmployeeID IN (#ValueList(SearchResults.Key)#)

but how do I do it using CFStoredProc..

here's what I came up so far (with errors I might add ;-)



<cfstoredproc procedure=&quot;EmployeeSearch&quot; datasource=&quot;#application.ds#&quot;
username=&quot;#application.user#&quot;
password=&quot;#application.pwd#&quot;>
<cfprocparam type=&quot;in&quot; cfsqltype=&quot;CF_SQL_Varchar&quot; dbvarname=&quot;@Keys&quot; value=&quot;#ValueList(SearchResults.Key)#&quot;>
<cfprocresult name=&quot;EmployeeResults&quot;>
</cfstoredproc>


In SQL, here is what I got...

@keys nvarchar(99) = null

AS

SELECT * FROM Employees WHERE EmployeeID IN (@Keys)


Error message displayed is:

---------------------
ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '2,18' to a column of data type int.
-----------------------


So how do I make them integers?

Employeeid is set to autonumber and integer type (primary key)

thoughts?

thanks

 

[jalbao]

Set your cfsqltype to CF_SQL_INTEGER