Professional Web Applications Themes

Can "Computed Column" be a stored procedure in SQL Server 2000? - Microsoft SQL / MS SQL Server

I am looking forward suggestions and solutions. I have a table and would like to add a "computed column" for reporting performance reason. The value of the column is calculated based on the columns on other tables. For example: I have tables A, B, C. A inclues columns a_id, b_id, a_name B inclues columns b_id, b_name, b_insert_timestamp C inclues columns a_id, computed column c_computed. The c_computed is defined as something like: if (b_name is presented in B and b_insert_timestamp < "Dec. 31, 2002" and C.a_id = A.a_id and A.b_id = B.b_id) { b_name } else { a_name } Or is ...

  1. #1

    Default Can "Computed Column" be a stored procedure in SQL Server 2000?

    I am looking forward suggestions and solutions. I have a table and
    would like to add a "computed column" for reporting performance
    reason. The value of the column is calculated based on the columns on
    other tables. For example:

    I have tables A, B, C.

    A inclues columns a_id, b_id, a_name
    B inclues columns b_id, b_name, b_insert_timestamp
    C inclues columns a_id, computed column c_computed.

    The c_computed is defined as something like:
    if (b_name is presented in B and b_insert_timestamp < "Dec. 31, 2002"
    and C.a_id = A.a_id and A.b_id = B.b_id)
    {
    b_name
    }
    else
    {
    a_name
    }

    Or is it a better that I have c_computed as a physical column and
    calculated at insert time?

    Thank you very much!
    Bill
    Bill Guest

  2. #2

    Default Re: Can "Computed Column" be a stored procedure in SQL Server 2000?

    Bill,

    A computed column can only use information of the same row. So your
    'computation' cannot be done with a computed column, because it
    references different tables and rows.

    Creating a physical column and filling it doesn't sound so bad...

    Gert-Jan


    Bill wrote:
    >
    > I am looking forward suggestions and solutions. I have a table and
    > would like to add a "computed column" for reporting performance
    > reason. The value of the column is calculated based on the columns on
    > other tables. For example:
    >
    > I have tables A, B, C.
    >
    > A inclues columns a_id, b_id, a_name
    > B inclues columns b_id, b_name, b_insert_timestamp
    > C inclues columns a_id, computed column c_computed.
    >
    > The c_computed is defined as something like:
    > if (b_name is presented in B and b_insert_timestamp < "Dec. 31, 2002"
    > and C.a_id = A.a_id and A.b_id = B.b_id)
    > {
    > b_name
    > }
    > else
    > {
    > a_name
    > }
    >
    > Or is it a better that I have c_computed as a physical column and
    > calculated at insert time?
    >
    > Thank you very much!
    > Bill
    Gert-Jan Strik Guest

  3. #3

    Default Re: Can "Computed Column" be a stored procedure in SQL Server 2000?

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are. Do you get specs this vague at your job, where tehy are
    paying you money? Here is my wild guess, one of several possible ones.

    CREATE TABLE A
    (a_id INTEGER NOT NULL PRIMARY KEY,
    b_id INTEGER NOT NULL
    REFERENCES B(b_id)
    ON UPDATE CASCADE,
    a_name CHAR(15) NOT NULL);

    CREATE TABLE B
    (b_id INTEGER NOT NULL PRIMARY KEY,
    b_name CHAR(15), -- no specs, so nullable assumed
    b_insert_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

    Use a view, so the data is always current.

    CREATE VIEW AB(ab_id, ab_name)
    AS SELECT B.b_id, COALESCE(B.b_name, A.a_name)
    FROM B LEFT OUTER JOIN A
    ON A.b_id = B.b_id
    AND B.b_insert_timestamp < '2002-12-31';

    Also, look up the proper format for dates in SQL and the meaning of
    double quote marks.

    --CELKO--


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  4. #4

    Default Re: Can "Computed Column" be a stored procedure in SQL Server 2000?

    Check out my presentation on UDF's and computed columns...

    [url]www.sqlserverfaq.com?kbid=649[/url]

    You may be able to use a computed column based from a UDF.

    --
    Tony Rogerson
    SQL Server MVP
    [url]http://www.sqlserverfaq.com?mbr=21[/url]
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Rogerson Guest

Similar Threads

  1. SQL Stored Procedure Problem "Single Quotes"
    By tranzformerz in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 7th, 05:08 PM
  2. Passing "%" wild card to stored procedure
    By john Smith in forum ASP Database
    Replies: 4
    Last Post: March 4th, 07:04 PM
  3. Replies: 28
    Last Post: October 21st, 04:48 PM
  4. Replies: 7
    Last Post: September 11th, 07:03 PM
  5. Help -- how to execute "computed Perl code"
    By Steve D in forum PERL Miscellaneous
    Replies: 13
    Last Post: September 2nd, 10:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139