SP for an Ad-hoc query

[SteveDingle]

Heya All,

Wondering if anyone could point me to a resource (books/articles/faq's) on the best practices for creating SP's that allow for ad-hoc queries.

Basically I have a front end which allows a user to build there own "where" clause. The field list and JOINs condition would always be the same but the where clause could contain a 1 field comparison or a complex clause with ANDs/ORs etc...

Thanks,
Steve

 

[vbkris]

take a look at the following:
1. EXEC command
2. sp_executesql SP

Known is handfull, Unknown is worldfull

 

[SteveDingle]

Thanks for the info vbkris.

Would you know off hand which would get better performance via an ODBC connection and a fron end

1) Calling an SP which contains the fields and joins etc.. but the where clause passed in as a parameter then using sp_executsql

2) build the entire SQL Select statement in the front end and pass it directly to SQL Server (i.e. as a SELECT...)

Thanks,
Steve

 

[vbkris]

option 1 would be better. atleast there is a chance theat the query plan will be cached...

Known is handfull, Unknown is worldfull

 

[SQLSister]

The only way to know for sure on something like this is to test both options.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief