Solved

Select all or just one

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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