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

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

You should create a view or a materialised query table (MQT) rather than a stored procedure.

OK thanks guys, I'll have a look into both of those and see which works best.

Thanks

Richard