T-SQL: Recursion and circular references

[balabaster]

Hi,

I have a couple of tables:

Units(
  Unit_PKey Int Identity(1,1) Primary Key,
  Unit_Name nvarchar(8),
  Unit_Description nvarchar(32)
)

1, lb, Pounds
2, cwt, Hundredweight
3, kg, Kilograms

Conversions(
  Cnvrt_PKey Int Identity(1,1) Primary Key,
  Cnvrt_FromUnit Int Foreign Key References Units.Unit_PKey,
  Cnvrt_ToUnit Int Foreign Key References Units.Unit_PKey, 
  Cnvrt_Formula
)

I am trying to build a recursive stored procedure that will handle transitive relationships, but I'm having some issues accounting for potential circular references.

Say I have the following table data (I will display the unit names with their keys in brackets so it's easier to follow.)

1, lb (1), cwt (2), {FROM}/100
2, cwt (2), lb (1), {FROM}*100
3, lb (1), kg (3), {FROM}/2.2046
4, kg (3), lb (1), {FROM}*2.2046

Obviously there is no formula in this table accounting for the conversion from kg to cwt, but a transitive relationship exists, such that (kg*2.2046)/100 converts kg to lbs.

In an ideal world, a formula would be in the database that allows the conversion directly kg*0.022046, and for performance purposes, I would push the users of this data to have formulae for direct conversions, but in the event someone's asleep at the wheel and no direct formula exists, I would like the system to build this transitive relationship.

A recursive procedure is easy enough to build, but I'm wondering what would be the best way to handle potentially disasterous circular references... I thought of passing up a list of previously checked "FromUnit"s thus allowing a select where the Cnvrt_ToUnit is not found in that list, and thus avoiding that scenario... the question is, what is the best way to pass this list up through the call stack?

My first thought is a temporary table, but:

• A global temp table (##MyTempTable for instance) is visible across the whole system... what happens if multiple people call this procedure at the same time?

• A local temporary table #MyTempTable isn't visible to each level of the call stack

• A table variable can neither be passed as an input or an output to a stored procedure...

So where do I go from here? Any thoughts would be most welcome.