>- see footer for list info -< Gene you star! Don't know how it works but it does, if you have got a couple of mins, can you give me a quick break down of effectively what it is doing?
Lee -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gene Brown Sent: 04 February 2005 14:48 To: Coldfusion Development Subject: RE: [CF-Dev] Stored procedure in CF >- see footer for list info -< Lee, Try this, set the VARCHAR lengths as you see fit for your purpose, bearing in mind the total length will include your separators as well. CREATE PROCEDURE spr_GET_USER_GROUPS_MEMBERS @MemberIDs VARCHAR(256) AS DECLARE @sql VARCHAR(512) SELECT @sql = 'SELECT * FROM LKU_Addresses_Groups WHERE GroupID IN (' SELECT @sql = @sql + @MemberIDs SELECT @sql = @sql + ')' EXEC (@sql ) GO Cheers Gene -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam Sent: Friday, February 04, 2005 2:40 PM 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 > >-< > _______________________________________________ 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 >-< _______________________________________________ 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 -< _______________________________________________ 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 >-< _______________________________________________ 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 -<
