Select all records with parameter

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)

Select all records with parameter

Author

Topic

robinson crusoe
Starting Member

1 Post

Posted - 2009-07-08 : 14:54:44

This is my stored procedure;
Declare @parameter int;
if @parameter = 0
begin
Select * from table
end
else
begin
select * from table where parameter = @parameter
end

Is there a way to write this in one query?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-08 : 19:08:05

Edited to account for NULL parameter values

declare @t table (parameter int, col varchar(10))
insert into @t (parameter, col)
select 0, 'a' union all
select 1, 'b' union all
select 2, 'c' union all
select null, 'd'

declare @parameter int
select @parameter = 0

select * from @t 
where (@parameter = 0 and isnumeric(isnull(parameter, 0))=1)
OR (@parameter != 0 and parameter = @parameter)

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources