restriction in sql clause

[gaurav.shetti]

say i have a query of this form

select * from cases where case_id in (a,b,c, .... )

What is the maximum no of elements or ids i can have within those brackets ?

Is there any restriction ?

[mark]

There's no coded limit on the number of items in an in.
The SQL statement itself may be limited to 8k characters so that's a limitation if you put the values inline as literals. You can use a parameter to get around that limit as well as avoid SQL escapement issues.

<strfmt "%s," $cases>
<$qcases=(convert($ret, 'strlst' ))>
<SQL "select * from cases where case_id in ($qcases)">

[gaurav.shetti]

I tried using what mark had suggested. But this is the error i got
SQL command too large

I guess the variable $qcases itself is greater than 8K characters. Does that matter?

[gaurav.shetti]

Okay i tried this
<strfmt "%s" $grpperson>
<$qgroupids=(convert($ret, 'strlst' ))>
<SQL ROW SKIP=$skip MAX=$maxRecordsPerPage
$startquery
$indexquery
"ohr_id in (" $qgroupids ")"
$endquery
>


$grpperson is a string like .. 124,124325,1233425,1342, ...

still i am getting error : SQL command too large

[mark]

Oops, also <strfmt "%s," $grpperson> should be <sum "%s," $grpperson> to get all elements in the $grpperson array instead of only the first.

If you're getting the too long msg with the strfmt that seems to imply you already have everything concatenated into $grpperson.

[gaurav.shetti]

yes $grpperson has everything concatenated. That is values are stored as 1,2,3,4,5 into $grpperson.
So when we do a select and we get comma separated ids.

Do i still have to do
<strfmt "%s," $grpperson> and
<sum "%s," $grpperson>

[gaurav.shetti]

varchar

[gaurav.shetti]

Wont you get 2 commas at the end once you do
<strfmt "%s," $grpperson> and
<sum "%s," $grpperson>

so it wont be 1,2,3,4,5,
it would be 1,2,3,4,5,,
is that fine ?

[mark]

Msg 8 says use <sum> not <strfmt>.