>- see footer for list info -< There is, you can use dynamic SQL but it is inefficient (server wise)
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Russ Michaels (Snake) Sent: 04 February 2005 23:48 To: 'Coldfusion Development' Subject: RE: [CF-Dev] Stored procedure in CF >- see footer for list info -< There has to be an easier way use use a list with an IN clause, afte rall that is what it is intended for. > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Lynch > Sent: 04 February 2005 15:14 > To: 'Coldfusion Development' > Subject: RE: [CF-Dev] Stored procedure in CF > > >- see footer for list info -< > Here's a simple loop in SQL > > DECLARE @list VARCHAR(100), @loop BIT, @item INT > > SET @list = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15' > SET @loop = 1 > > WHILE @loop = 1 BEGIN > > -- If we can't find the delimiter the last element is > all that's left of the list > IF CHARINDEX(',', @list, 1) = 0 BEGIN > > SET @item = @list > SET @loop = 0 > > END ELSE BEGIN > > -- Extract the first element > SET @item = SUBSTRING(@list, 0, CHARINDEX(',', > @list, 1)) > > -- Remove the first element from the list > SET @list = SUBSTRING(@list, LEN(@item) + 2, LEN(@list)) > > END > > SELECT @item 'List Item' > > END > > This will mean you can get around the EXEC only permission. > > -----Original Message----- > From: Lee Fortnam [mailto:[EMAIL PROTECTED] > Sent: 04 February 2005 15:11 > To: 'Coldfusion Development' > Subject: RE: [CF-Dev] Stored procedure in CF > > > >- see footer for list info -< > I can confirm this as at present, all my user has is Stored > Procedure rights > and nothing on the actual tables, this complained about the > select on the > LKU table and as a result I had to grant it. > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Lynch > Sent: 04 February 2005 15:10 > To: 'Coldfusion Development' > Subject: RE: [CF-Dev] Stored procedure in CF > > > >- see footer for list info -< > It also has the consequence of needing select permissions for > the user cf > connects with. > > (Hopefully someone can confirm this :O) > > Ade > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 04 February 2005 15:00 > To: Coldfusion Development > Subject: RE: [CF-Dev] Stored procedure in CF > > > >- 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 > >-< > _______________________________________________ > > 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 -<
