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

  • Pergunta

  • 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/ 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

    quarta-feira, 30 de janeiro de 2013 16:11


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


    create #mytable


    insert #mytable
    exec myStoredProcedure
    Select * from #mytable


     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!

    quinta-feira, 31 de janeiro de 2013 06:26