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]

Re: Alternative to dynamic sql?

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--]

Re: Alternative to dynamic sql?

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

[Erland Sommarskog]

Re: Alternative to dynamic sql?

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

Jenny (jenny@megasol. se) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp