Hey Rodd,
Have you thought about creating a string of sql and executing that in
your stored proc?
eg
EXEC ('select x from whereever where clinic id in (' + @clinicid +
')')
?
assuming your ints are comma delimited.
>>> [EMAIL PROTECTED] 28/01/2005 12:04:30 pm >>>
What is the most efficient way to pass a set of integers into a stored
procedure? Im currently passing in the values via a list as a varchar
-:
<cfprocparam type="in"
dbvarname="@clinicid"
cfsqltype="CF_SQL_VARCHAR"
value="#trim(request.clinicid)#">
And then parsing and converting the values in the sp like so -:
/* sp snippet */
if (datalength(@clinicid)>0)
/* parse */
begin
DECLARE @clinicid_id varchar(10)
SET @clinicid = LTRIM(RTRIM(@clinicid))+ ','
SET @Pos = CHARINDEX(',', @clinicid, 1)
IF REPLACE(@clinicid, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @clinicid_id =
LTRIM(RTRIM(LEFT(@clinicid, @Pos - 1)))
IF @clinicid_id <> ''
BEGIN
INSERT INTO
#tempClinicId (clinicid) VALUES (CONVERT(int ,
@clinicid_id))
END
SET @clinicid = RIGHT(@clinicid,
LEN(@clinicid) - @Pos)
SET @Pos = CHARINDEX(',',
@clinicid, 1)
END
END
end
Is there any other way to do this from a CF page? This approach works
ok
for a small amount of data but is inefficient for a large data set.
Any
ideas? Is there a better approach to use?
thanks in advance
rod
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
