>- see footer for list info -< This will work, but dynamic SQL like this is inefficient in as far as the DB is concerned.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam Sent: 04 February 2005 14:55 To: 'Coldfusion Development' Subject: RE: [CF-Dev] Stored procedure in CF >- 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 -< This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com _______________________________________________ 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 -<
