Doubt in Select statement - 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

Transact-SQL (2000)

Doubt in Select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-11 : 08:28:15

Vipin writes "I will show my sql query first.

select * from table where id=@id

My problem is such that i have 3 id (1,2,3) when i pass value 1 i get the respective fields, when i give 2 and 3 also i get the result but I have to pass one parameter "Show all" which has to show all the fields in the given table irrespective of the id. How should be the SQL Query then."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-11 : 08:40:36

If you want to have all values returned no matter what the ID is then you wouldn't use the id = @id part of the query. You would simply have

select * from table

If this is a stored procedure you can either create a second stored procedure that will do that or put some kind of check in place

if @id IS NULL
select * from table
else
select * from table where id=@id

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-11 : 10:11:07

Other solution can be...

select * from table
where 1 = (case when @id = 'Show All' then 1 
               else case when id = @id then 1 else 0 end
          end)

Harsh Athalye
India.
"Nothing is Impossible"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 10:12:03

or

select * from tbl where (id= @id or @id = 'Show all')

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-11 : 13:03:58

or

select * from tbl where (id= @id or @id is null)

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 13:20:03

Madhi,

quote:
Originally posted by AskSQLTeam

Vipin writes ....
...
I have to pass one parameter "Show all" which has to show all the fields in the given table irrespective of the id. ...

He is not passing NULL

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-14 : 12:59:12

Yes. Thanks for the correction

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources