Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Stored Procedures & Queries

    Hello,

    I am relatively new to stored procedures and would be grateful of some advice on the subject. Basically, I currently have blocks of sql that are performed numerous times based on different application requirements. Each block of sql is called at different times from different places and as such would seem good candidates to turn into stored procedures. However, athough the core sql is always the same, I apply different filtering selection criteria based on different circumstances - this may often mean applying 4 or five additional 'and blah.blah = wiz.wiz' statements on the end of the normal block. So what I am wondering is whether it is be possible to do this by running the core sql as a stored procedure and then appending my additional criteria on the end and if so how would this work?

    select * from (stored procedure)
    where this = that
    and ping = pong?

    Is that a viable / recommended way of using stored procedures and would it lead to any kind of performance upgrade on my most commonly executed statements?

    Again, I am pretty new to stored procs and am reading up on them currently, but any pointers would be useful.

    Thanks

    Richard

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here are two ways that you can do this in a SP.

    1) use dynamic SQL in the SP to build the query. There have been some other recent posts in this forum on this subject.

    2) Use a temp table to store the core SQL. Then based on the other criteria,
    you delete from the temp table. At the very end you just open a cursor that is just select * from temp table.

    Andy

  3. #3
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Looking at your example

    You should create a view or a materialised query table (MQT) rather than a stored procedure.
    jdey@macehill.co.uk
    http://www.macehill.co.uk

  4. #4
    Join Date
    Nov 2003
    Posts
    17
    OK thanks guys, I'll have a look into both of those and see which works best.

    Thanks

    Richard

Posting Permissions

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