Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-19 : 08:31:39
|
| gleceper writes "hello, is there a way i can AVOID USING a dynamic sql inside a stored procedure (SP) i if want to: SELECT * FROM MyTable WHERE @SearchField = @SearchKey ?the idea is simple: my sample SP returns record sets from a given table as a result from querying @SearchKey from a variable field (@SearchField). one of my the option is to use IF..THEN conditions to test every possible @SearchField value. however, i thought my SP would be too lengthy if @SearchField would have 10+ possible values. the management of my SP codes would also be tedious assuming i want to update the result statement block (as i would have to change each statement blocks at the 10+ IF..THEN conditions!).the objective is to avoid both dynamic SQL and elongated SP codes due to its known disadvantages. would appreciate feedback. thanks. Ian " |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-19 : 08:48:06
|
| http://www.sommarskog.se/dyn-search.html--Frankhttp://www.insidesql.de |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-19 : 09:29:10
|
| I start off with a query that uses dynamic sql for all searches then use if...then statements for the common searches.If there are lots of search conditions then I will build a bit map to pick out the if/then branches and also use it for the dynamic sql.Putting the different searches in different SPs may help with cache usage.For searches I just return the PKs and join to that for the result - you could do that for the update too.You can alsowhere (myfld1 = @SearchKey or @SearchField <> 'myfld1')and (myfld2 = @SearchKey or @SearchField <> 'myfld2')...Probably best to use a combination of techniques.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-19 : 09:46:08
|
for character-type fields, i think it's usually easiest and quickest to use this:where fld1 LIKE ISNULL(@Fld1, '%') AND fld2 LIKE ISNULL(@Fld2, '%') keeps things quicker and will use indexes and all that good stuff. But as Nigel states, the true "boolean logic" way to handle this situation is to use the OR's ... sometimes, if you have too many of them, though, SQL Server will not give you a fully optimized query I have found and you can get better performance converting some conditions to using LIKE.Try it out !- Jeff |
|
|
|
|
|
|
say no to dynamic sql