May 20, 2002 at 6:01 pm
I have a stored procedure consisting of multiple IF statements that union the various queries together based on the user's criteria. For example, there are three queries A, B and C. The stored procedure needs to output A, A UNION B, A UNION B UNION C, B, B UNION C, or C depending on the user's criteria. Right now, the stored procedure has IF statements for each of these combinations. This makes the stored procedure fairly long and difficult to change since the same queries are repeated multiple times in the different IF blocks.
Is there a better way to implement this type of stored procedure?
May 20, 2002 at 7:50 pm
It won't make the process of changing easier but it will boost performance. Instead of doing 1 long SP create an SP for each process then in the main SP do like so
CREATE PROC ip_mainbit
--Variables here
AS
SET NOCOUNT ON
IF x=blah
BEGIN
EXEC ip_doaonly
END
ELSEIF x=blah2
BEGIN
EXEC ip_doabunion
END
ELSEIF x=blah3
BEGIN
EXEC ip_dobonly
END
This will make things better to some extent thou as you have bits to read instead of one long cludgy piece of code. The reason for seperation is it will allow seperate execution plans to be generated and stored with their SP's for the queries to take advantage of.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 9:22 pm
I agree with Antares. This is the best way to set this up and modularize the code. You might even resuse some of the individual sprocs for something else.
Steve Jones
May 20, 2002 at 11:48 pm
Thanks for the info. I have another wrinkle to add. I would like to use the output or the union queries in another statement. Specifically, I need to apply paging logic to the result set (so it only returns x records at a time) and also add additional rows to the result set based on the values in two columns (this is done by joining to a sequence table of dates). I have the SQL for both of these applications in other stored procs that don't have the multiple IF blocks. I'm not sure how to best implement them in this situation.
I could add this additional logic to each of the new sub procs but this would be adding redundant code. Is there a way to apply this additional SQL to the output of the overall IF block?
Thinking out loud...Could each of the sub stored procedures write to a temporary table and have the calling stored procedure reference the temp table for its additional joins? If this is possible would it advisable to have each of the sub procs simply output the primary key in the temporary table and then have the calling stored procedure do a join on the main table to retrieve the particular columns. This way if I add additional columns in the future these would have to be added only in the calling stored procedure and not all of the sub sp's.
Sorry about the convoluted nature of this request, but this one has me scratching my head.
May 21, 2002 at 4:06 am
quote:
I need to apply paging logic to the result set (so it only returns x records at a time)
I am not sure what yo are doing here. If this is being done to an app you are better of allowing the object build a cursor to handle the data and paging. If done for similar to QA then allow all the records to go. The reason is if the data is not all returned and they want to see the next peice, doing this on the server like this will have the detremental effect of increasing your reads significantly, although SQL cursor are considered bad ADO based cursors can hold the data and wind forward or backward depending on the type of cursor while retaing the data set and thus need not call the data repeatedly from their tables. You should still be able to do as you are doing now but not the best way I can see unless I misunderstand you.
quote:
also add additional rows to the result set based on the values in two columns (this is done by joining to a sequence table of dates).
Personally I would write in each SP to make sure I bennefit from the execution plan. However you can put your data into a temp table (see next statement) and do this to them temp table set like you do any other table just as long as you keep consitant with your output. Otherwise you will really need to do in the sub SP. Redundant code is not that bad of an issue no matter what anyone states, "reuse is aimed for but not at the cost of performance" is the way to look at redundant code.
quote:
Thinking out loud...Could each of the sub stored procedures write to a temporary table and have the calling stored procedure reference the temp table for its additional joins? If this is possible would it advisable to have each of the sub procs simply output the primary key in the temporary table and then have the calling stored procedure do a join on the main table to retrieve the particular columns. This way if I add additional columns in the future these would have to be added only in the calling stored procedure and not all of the sub sp's.
I suggest if you use a temp table you design to handle the outputs. The reason is a temp table cannot be created in a sub SP as that is out of scope from the top SP. However when created in the main SP the temp table exists since the main SP controls the scope of the work, all sub SPs can see it. Keep that in mind but it is feasible to do.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 9:50 am
For the paging issue: this was done in an effort to reduce network traffic. I intially developed the stored proc to return all applicable rows and used a client side cursor to handle the paging. However, several of my pages can return thousands of rows. If the user only wanted to see 10 to 20 of them (and neglected to put in relevant search criteria) it seemed disadvantageous to have all the extra data travel over the network.
I'll try your suggestion of using temp tables.
Thanks you your help!
May 21, 2002 at 10:01 am
quote:
For the paging issue: this was done in an effort to reduce network traffic.
I understand that. If this is the case to decrease network load but you have to weigh one against the other which has the worse issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 1:24 pm
Simple:
Add to the first query (A) the condition to be present, so it becames A'. The same with B and C.
So now you can use:
A' union all B' union all C' and ypur parameters will define the result set.
For example, if your store has 3 parameters, ... @showA int, @showB int, @showC int ... and a @showA>0 means 'include A'
Then convert A into A' adding to the 'where' clause the condition: 'and @showA>0'
The same thing with B and C...
quote:
I have a stored procedure consisting of multiple IF statements that union the various queries together based on the user's criteria. For example, there are three queries A, B and C. The stored procedure needs to output A, A UNION B, A UNION B UNION C, B, B UNION C, or C depending on the user's criteria. Right now, the stored procedure has IF statements for each of these combinations. This makes the stored procedure fairly long and difficult to change since the same queries are repeated multiple times in the different IF blocks.Is there a better way to implement this type of stored procedure?
Edited by - JorgeCordero on 05/23/2002 1:26:43 PM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply