I am in the process of creating a SP. I know what I want to accomplish, but I have atleast 2 ways I can solve the problem.
Problem:
I want to pass a parameter @ID to a SP. @ID is PK in the Master table and the Master table contains a binary field that I want to return. @ID is also FK in the Child table and the Child table also contains a binary field which I want to return for all rows matching FK = @ID (There should be exactly 41 rows matching this criteria). Now here comes the "problem": The total amount of binary data that I want returned is 42,238 bytes (254 + 41*1024).
Solution 1:
I could return a dataset looking something like this:
id data
0 <binary> (254 bytes)
1 <binary> (1024 bytes)
2 <binary> (1024 bytes)
.
.
.
41 <binary> (1024 bytes)
Solution 2:
I could return a dataset looking something like this
master_data child_data
<binary> (254 bytes) <image> (41,984 bytes)
I am currently leaning towards using solution #2 since a less data is transfered back to the client.
Are there any other (potentionaly better) solultions that I am missing here?
How would I go about appending binary data to an image field (like I am trying to do in solution #2)?
Thanks,
Einar
UPDATE: After playing around with this a little bit more I found that I can return the following dataset:
data_00 data_01 data_02 ... data_41
<binary> (254 bytes) <binary> (1024) <binary> (1024) ... <binary> (1024)
I originally rejected this solution because I didn't think it was possible (because of the 8000 bytes record size limitation). Any thoughts on the last scenario?
Semper ubi sub ubi.