dynamic where clause - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

dynamic where clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-28 : 08:18:52

Stephen writes "How can one specify a dynamic where clause without using dynamic sql (obviously won't work but for example):

create procedure se_sproc
@CustomerID varchar(10)
select * from customer
case when
@CustomerID is not null
then
where CustomerID = @CustomerID
end

I know I could do this:

create procedure se_sproc
@CustomerID varchar(10)
if @CustomerID is null
begin
select * from customer
end
else
begin
select * from customer where CustomerID = @CustomerID
end
go

One example I found was to do:

select * from customer
where (CustomerID=@CustomerID or @CustomerID is null)

The only problem is I am not sure what potential performance (or other problems) might exist using this method."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-28 : 08:35:20

Instead of case you can use the last method as it will be faster

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources