Solved

Select all or just one

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 38
Usage Scenarios for Extended Events? 1 25
Haw to apply join on 2 tables with this scenario 4 22
TSQL previous 5 26
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now