CODEWITHSUNDEEP
sql
kalabo
kalabo

Reputation: 564

Dynamic SQL Where statement

I am trying to have a single SQL query that will use (if it is set) a parameter to get a specific id of a table otherwise if its NOT set..then to do a where in clause (as the example below)

Declare @projectId varchar(255) = '31DEC523-1234-1234-1234-0E6D5CFEC249'

select * from projects where projects.id = @projectId

select * from projects where projects.id in (select top 2 id from projects)

How could i join the above into a single query?

I have tried a version of the following which is like 50% of the way there..but i cannot figure out how to do the optional in (SELECT)

Declare @projectId varchar(255) = '31DEC523-F19A-4B13-A73C-0E6D5CFEC249'

select * from projects 
WHERE  
projects.id LIKE @projectId+'%'
OR      @projectId IS NULL

Upvotes: 1

Views: 70

Answers (2)

Alex B.
Alex B.

Reputation: 2167

Untested:

select * from projects 
WHERE  
projects.id LIKE @projectId+'%'
OR  (@projectId IS NULL 
     AND projects.id IN (select top 2 id from projects)

Like Gordon wrote in his answer an Order By id would make alot of sense.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You could just add top (2) to the query:

select top (2) p.*
from projects p
where p.id LIKE @projectId + '%' or @projectId IS NULL;

This assumes that the like returns one row (or at least never more than 2).

If it could, then union all might be simplest:

select p.*
from projects p
where p.id like @projectId + '%'
union all
select top (2) p.*
from projects p
where @projectId is null;

Note that this returns two arbitrary project ids -- just like your query. If you want two specific ones, then use an order by.

These also make the assumption that id is unique in projects -- which seems like a very reasonable assumption.

Upvotes: 1

Related Questions