Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to return the extended row (that joins the securitymaster with the sub table in a UDF or Stored Proc)?

In my example, I will abbreviate the securitymaster table as A, the subtables are B,C,...,Z. Each subtables has its own specific columns. Let's further imagine the A has the first column a1 which is the PK and the last column a10 that holds the value B,...,Z (I.e. a FK to the subtable name). The number of columns in the sub table differ and will normally be specific to each table, however some tables may share the same column names.

If I have a securityID, say @sID, then, I will need to join the table as defined in A.a10 where A.a1 = @sID. I will then need to present select * from F (let's imagine A.a10 was equal to F). I have tried with Execute, but people say its a bad method, also I don't know how to return the value or the combined row from an EXEC within a sp. UDF looks impossible as they can not be dynamic. An example would be really useful as I feel this should be a fundamental tenant of any DB?

Many thanks

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:24

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

This is not entirely correct design (if I understand correctly what you have). The Foreign Keys constraints should be from a subtype tables, referencing the supertype table. Not the other way around, as your attempt.

So, you need one additional column in every subtype table that holds this specific value (B, C, ... or Z) and is part of the Foreign Key.

See this answer by @gbn, Option 3, for details on how this can done: NULLs in a composite primary key - SQL Server

share|improve this answer
add comment

Normally, you'd create a view for each subtype. Do the join between the "super" table and the "sub" table in the view.

If you make the views updatable, client code can just use the views.

share|improve this answer
add comment

If i understand your question you can

  1. Construct your query dinamically with syscolumns support and runs it with EXEC.

  2. SELECTs covering all the subtables structures with UNION.

share|improve this answer
    
Do you have an example? I would appreciate an example where I can then perform select * from myFuntionORProc(@sID ). Can you point in the direction for me to construct the myFuntionORProc example? Many thanks, –  user5379 Dec 28 '11 at 13:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.