Solved

Select all or just one

Posted on 2011-09-08
4
233 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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

830 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