none
How to use a stored procedure in DDL layer? RRS feed

  • 问题

  • i construct my database using a multi layer method,skiping the detail,now i got some problems:

    how i suppose to orgnize the layers?

    1.datatype->stored procs->views->report/ado....like a upsidedown tree

    a problem in this flow, you can't put exec xxx into a select clause building up a view

    2.datatype->UDFs->views->reports...upside down tree structure 

    problem:UDF performance not as good as procedure

    there r other posibilities as well, like form a ddl layer with view ,even worse performance,

    i still want a way out using the 1 one i mentioned

     
    2013年1月30日 16:11

答案

  • I afraid u can't  call a sp in the view directly, but u can definite the following sp instead,

    CREATE PROCEDURE  XXXX
     AS
    BEGIN

    create #mytable

    .......

    insert #mytable
    exec myStoredProcedure
    Select * from #mytable

    end

     BTW, for SQL server , the performance killer is the query plan/algorithm for the statements  instead of the database object type :such as  Sp Or UDF.



    Please click the Mark as Answer button if a post solves your problem!

    2013年1月31日 6:26