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 |
|
alesk
Starting Member
2 Posts |
 Posted - 2004-01-12 : 07:22:26
|
| I have a stored procedure with input parameter, which is used in where condition. When I set the parameter the query returns matching results (OK). Now when I leave the parameter empty I want to return all dataset, like there was no where condition. How to write where condition for this to work (not hidering performance, ofcourse)I see 3 options:- flow control inside procedure (IF ... THEN 1st query ... ELSE 2st query) - dont like it, same query many times.- where condition something like that: where (table.id = @id) or (@id is null) - major performance drop.- case statement in where condition -complicated where condition when many parameters + performance drop (comparing when caling one query with where condition and another without it)Any sugestions? Plz? Universal solution? :) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-12 : 08:22:15
|
| YTou could put null in the parameter for all andwhere table.id = coalesce(@id,table.id)But that will still give a performance hit.I think the only way round this is optimiser hints or dynamic sql.I use flow control for commonly used queries combined with dynamic sql sometimes if the system allows it. Keep a list of the options catered for via flow control and use dynamic sql for the rest - then can add options to flow control at will.==========================================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. |
 |
|
|
alesk
Starting Member
2 Posts |
Posted - 2004-01-12 : 08:51:03
|
| I used to use dynamic sql for this to. But it pops up other isues.SQL Server don't create execution plan (I think this is only for EXEC, not for sp_execute), there are problems with formating datetime,(I used to use the "general" datetime format that SQL server is supposed always to understand, but on the newsgroups there are many different sugestions which this format is:)), and another "problem" is: I can't simply copy/paste this kind of stored procedure into view, for adding a table, field, condition... - not a problem with simple queries.What optimizer hints did you mean, that I can use. For example? |
|
|
|
|
|
|
|
|
SELECT query for returning one OR all records