by Hi,
I am trying to return a shaped recordset from SQL7 and I hope to use stored
procs.
I know that you cant use a stored proc to generate the shaped recordset, but
I was hoping to use something like this:
strSQL = ""
strSQL = strSQL & "SHAPE {usp_PointsAllocRuleGetById ?} "
strSQL = strSQL & "AS rsPointsAllocRule "
strSQL = strSQL & "APPEND ({usp_PointsAllocRuleProductsGetById ?} "
strSQL = strSQL & "AS rsPointsAllocRuleProduct "
strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"
as the commandtext.
It seems to work if the I only use one stored proc asn the parent, and
dynamic sql as the rest. eg:
' strSQL = ""
' strSQL = strSQL & "SHAPE {EXEC usp_PointsAllocRuleGetById ?} "
' strSQL = strSQL & "AS rsPointsAllocRule "
' strSQL = strSQL & "APPEND ({SELECT PointsAllocRuleId , ProductCode
FROM PointsAllocRuleProduct WHERE PARP.PointsAllocRuleId = ?} "
' strSQL = strSQL & "AS rsPointsAllocRuleProduct "
' strSQL = strSQL & "RELATE PointsAllocRuleId To PointsAllocRuleId)"
If anyone has any ideas on this I'd much appriate it.
Cheers
Gordon Staines