select question - 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 2005 Forums

Transact-SQL (2005)

select question

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-08 : 06:47:57

Hi,
I have this table with products and each product has a categoryid so:
ProductID Product CategoryID
1 Prod1 23
2 Prod2 41
3 Prod3 12

I want to make a SP where I can say
select * from products
where categoryid = @categoryid

Only when I enter a -1 as categoryid it should do
select * form products

Now, I know you can do this with
@categoryid int = -1

and then something like:

if @categoryid = -1
begin
select * form products
end
else
begin
select * from products
where categoryid = @categoryid
end

But I want to know an alternative
Since I want to add more parameters like this, and it becomes a bit of a mess then

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 06:50:36

[code]
select * from products
where categoryid = @categoryid
or @categoryid = -1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-08 : 06:54:50

Right, that was it....
Thanks.

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources