Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select all or just one

Posted on 2011-09-08
4
Medium Priority
?
243 Views
Last Modified: 2012-06-27
I have a table Table1 (Id, TText)
I have a parameter @Id.  If @Id is null I would like to return all rows, otherwise the row where Table.Id=@Id. How is best to do this?
0
Comment
Question by:johnkainn
  • 2
4 Comments
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36501531
if @id is null
begin
    select * from table1
else
   select * from table1 where id = @id
end
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 total points
ID: 36501533
Select * from tablename
where @id is Null OR Table.Id=@Id
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36501541
You could check out sommarskog.se on Dynamic Search Conditions in T-SQL.
It takes about an hour to read, but it is worth it, IMHO.

Or you could just do something like this

if @ID is null begin
   select * from Table1
end
else begin
   select * from Table1 where table1.id=@ID
end

or
select * from Table1 where table1.id=@ID or @ID is null

But only if your table is small! Bigger tables will take too long.

Best regards,
Henrik
0
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36501543
I meant
if @id is null
    select * from table1
else
   select * from table1 where id = @id

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question