this might help you Ali http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Qasim On 3/17/06, Ali Awan <[EMAIL PROTECTED]> wrote: > > I have a query that I want to convert to a stored procedure. > > The problem I am having is that the query has lists in it and I don't know > how to pass those through in the stored procedure. > > > > My query looks like this: > > > > [cfquery name="getOver4" datasource="mydsn"] > > SELECT DealerID, RecNumber, agent, NetPayable, TR, EntryDate, > EffectDate > > FROM Table A > > WHERE DealerID IN (#PreserveSingleQuotes(DealerListOver4)#) > AND > > agent IN > (#PreserveSingleQuotes(AgentListOver4)#) > > Group By agent, DealerID, RecNumber, EntryDate, EffectDate, > TR, > NetPayable > > ORDER BY agent, DealerID, RecNumber, EffectDate > > [/cfquery] > > > > My question is how to pass DealerListOver4 and AgentListOver4 to the > storedprocedure? > > Those lists are pretty long, sometimes they have close to a 100 ID's in > them. > > > > When I tried (unsuccessfully to convert this to a stored procedure this is > what I did: > > CREATE PROCEDURE [dbo].[CommissionsSelOver4] > > @dealerOver4 varchar(255), > > @FSROver4 varchar(255) > > AS > > SELECT DealerID, RecNumber, agent, NetPayable, TR, EntryDate, EffectDate > > FROM Table A > > WHERE DealerID IN (@dealerOver4) AND > > agent IN (@FSROver4) > > Group By agent, DealerID, RecNumber, EntryDate, EffectDate, > TR, > NetPayable > > ORDER BY agent, DealerID, RecNumber, EffectDate > > > > How can I correctly pass the lists to the stored procedure? > > Thanks, > > Ali > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235648 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
