>- see footer for list info -< Two way around this problem. Loop over the SP in CF passing one int at a time or pass the list of IDs as a varchar and loop over this in the SP casting each one as you need it.
If you go for the second option, look at the string functions in BOL to see how you can manipulate the ID list. CHARINDEX() is a good 'n'. Have a google for some solutions already out there. Ade -----Original Message----- From: Lee Fortnam [mailto:[EMAIL PROTECTED] Sent: 04 February 2005 14:40 To: 'Coldfusion Development' Subject: RE: [CF-Dev] Stored procedure in CF >- see footer for list info -< Get the following error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the nvarchar value '1,2' to a column of data type int. Presumably as the field is of time INT in the DB. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russ Michaels (Snake) Sent: 04 February 2005 14:37 To: 'Coldfusion Development' Subject: RE: [CF-Dev] Stored procedure in CF >- see footer for list info -< @MemberIDs int Also needs to be a varchar Russ > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam > Sent: 04 February 2005 14:25 > To: [email protected] > Subject: [CF-Dev] Stored procedure in CF > > >- see footer for list info -< > Hi All, > > I have a stored procedure which just queries a lookup table. > > Here is the store procedure: > > CREATE PROCEDURE spr_GET_USER_GROUPS_MEMBERS > @MemberIDs int > > AS > > SELECT > * > FROM > LKU_Addresses_Groups > WHERE > (GroupID IN (@MemberIDs)) > GO > > The value I am trying to pass into @MemberIDs is 1,2,3,4 (i.e. a list > of numeric values generated by a ListValue function. > > If is run the query from within SQL Server replacing @MemberIDs with > 1,2,3 it works fine, but receive nothing when placing it in a stored > procedure. > > Here is my stored procedure call: > > <!--- Get members for user ---> > <cfstoredproc procedure="spr_GET_USER_GROUPS_MEMBERS" > datasource="#request.dsn#" username="#request.db_username#" > password="#request.db_password#" returncode="Yes"> > <cfprocparam type="In" > value="#ValueList(res_GET_USER_GROUPS.GroupID,",")#" > cfsqltype="CF_SQL_INTEGER" variable="MemberIDs"> > <cfprocresult name="res_GET_USER_GROUPS_MEMBERS"> > </cfstoredproc> > > Anyone got any ideas why it would return 1 set of details directly > within SQL Server but nothing through a store procedure? > > Regards, > > Lee _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
