building dynamic query without dynamic query

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

building dynamic query without dynamic query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-09 : 08:56:16

Fréderic writes "A store proc makes a select on a table.

This proc accepts, say 2 parameters from a search form for filtering the select statement: @p1 and @p2

Both of them can be null depending the criteria selected on the search form.

I don' want to use a dynamic query

So I can do something like:

if @p1 is null and @p2 is null
select ...
else if @p1 is not null and @p2 is null
select ... where p1=@p1
else if @p1 is null and @p2 is not null
select ... where 2=@p2
else if @p1 is not null and @p2 is notnull
select ... where p1=@p1 and p2=@p2

of course this is tedious

Is there a way to do this with a single sql statement?"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 08:58:35

[code]
select ...
from ...
where p1 = coalesce(@p1, p1)
and p2 = coalesce(@p2, p2)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources