Complex stored procedure design - help me!!!

[Judy Buonocore]

I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.

I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.

My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?

Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?

Any insight would be a big help.
Judy

[Patricia Rotman]

I am not sure if this will help you or not, but one thing that we do a lot of is creating dynamic SQL statements. Basically you create a varchar variable, say @SqlStatement, to hold the sql statement. Then you build it using your incoming parameters. Once the string is composed you can use
EXEC(@SqlStatement) to run it. If you need to insert the results into a temp table you can do that via

Insert Into #whatever
Exec(SqlStatement)

------------
Judy Buonocore at 7/19/00 9:45:27 AM

I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.

I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.

My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?

Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?

Any insight would be a big help.
Judy

[judy buonocore]

Thanks for the info Patricia. Do you have any examples of code that do these things? Also do you know of any issues with security that I should know about, when using dynamic sql from a stored procedure?

Thanks again,
Judy


------------
Patricia Rotman at 7/19/00 5:34:03 PM


I am not sure if this will help you or not, but one thing that we do a lot of is creating dynamic SQL statements. Basically you create a varchar variable, say @SqlStatement, to hold the sql statement. Then you build it using your incoming parameters. Once the string is composed you can use
EXEC(@SqlStatement) to run it. If you need to insert the results into a temp table you can do that via

Insert Into #whatever
Exec(SqlStatement)

------------
Judy Buonocore at 7/19/00 9:45:27 AM

I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.

I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.

My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?

Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?

Any insight would be a big help.
Judy

Here is a simple example:

Select @CmdBuff= "Select count(*) from tblPointValue tv, TblReportPoints tr" +
" where tr.PointId = tv.PointId And tr.SPID = " + @KeyValue +
" AND tv.CollectionDateTime >= '" + @EndTimeStamp + "'"

Insert into #RecCount
Exec (@Buff1)


Some things you need to be careful of:
- make sure there are spaces before and after the keywords
- variables have to be character types, so if your parameters are not you
will need to convert them
- adding single quotes into the command string where appropriate

I don't know of any security issues with doing the EXEC.

------------
judy buonocore at 7/20/00 8:27:06 AM

Thanks for the info Patricia. Do you have any examples of code that do these things? Also do you know of any issues with security that I should know about, when using dynamic sql from a stored procedure?

Thanks again,
Judy


------------
Patricia Rotman at 7/19/00 5:34:03 PM


I am not sure if this will help you or not, but one thing that we do a lot of is creating dynamic SQL statements. Basically you create a varchar variable, say @SqlStatement, to hold the sql statement. Then you build it using your incoming parameters. Once the string is composed you can use
EXEC(@SqlStatement) to run it. If you need to insert the results into a temp table you can do that via

Insert Into #whatever
Exec(SqlStatement)

------------
Judy Buonocore at 7/19/00 9:45:27 AM

I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.

I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.

My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?

Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?

Any insight would be a big help.
Judy