Alternative to dynamic sql?

[Jenny]

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
Jenny

[David Portas]

Maybe:

SELECT *
FROM TableX
WHERE idnr = @lcIdNr OR @lcIdNr IS NULL
This article explains some of the things you should consider before using
Dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

--
David Portas
------------
Please reply only to the newsgroup
--

[--CELKO--]

SELECT *
FROM Foobar
WHERE idnr = COALESCE(@lcIdNr, idnr);

[Erland Sommarskog]

[posted and mailed, vänligen svara i nys]

Jenny (je***@megasol.se) writes:

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view.

Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.

For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp