>- see footer for list info -< I think whatever the final use of the list of IDs you still have to loop over them. Be it using them one at a time or putting them in a temp table and selecting from that. Either that or use dynamic SQL.
Ade -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005 _______________________________________________ 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 -<
