Subject How to convert set of integers into a BLOB in SP or UDF
Author Roman Rokytskyy
Hi All,

Does anybody have an idea how to convert set of integers (result of a
select) into a single BLOB?

Main goal is to minimize conversation overhead between client and
server. I have a table with ~100,000 records, and query selects
approx. 500 records, from which I am interested only in record PK
(INTEGER). Fetching 500 rows with 4 bytes of payload is not very nice

Is there any chance to convert that result set into one row that is
returned to a client in one go?

One solution that comes to my mind is to return VARCHAR(32000)
containing comma-separated list of IDs (it is generated by
concatenating PKs). However, in this case I limit maximum number of
IDs in select (32000 chars / avg. 5 char per ID = ~6,500 IDs). Even
this will work in my case (one can return more than one VARCHAR row),
it seems to be not very clean solution.

Any other ideas?

Thanks in advance,
Roman Rokytskyy