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:235644
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
