I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.
No comments:
Post a Comment