I am designing a SQL query to extract all records from a given table. But the trick here is that this logic is based on a numeric database field. So there are 4 choices: 0,1,2,3. If user selects 0,1, or 2, then my query returns rows with the specified value. But if they 开发者_JS百科choose 3, it should return all of the rows. How do I do this in SQL? I know if this was a string, I could do something like: WHERE = CASE WHEN = 3 THEN '%' ELSE END
But in this case, is an integer. Sounds relatively simple but I'm getting errors.
Try this:
SELECT *
FROM <YOUR_TABLE>
WHERE
(
<YOUR_COLUMN> = @InputValue OR
3 = @InputValue
)
Where @InputValue is the name of parameter sent to the query.
The simplest way is to do this:
select MyColumn
from MyTable
where ( MyValue = @MyParameter or @MyParameter = 3)
If your interested in better optimization, then you can do this, but it is less maintainable:
if (@MyParameter = 3)
select MyColumn
from MyTable
else
select MyColumn
from MyTable
where MyValue = @MyParameter
If I were forced to implement this functionality, then I would probably do this, just to make things clear:
declare @AllRecords nchar(1)
if (@MyParameter = 3)
set @AllRecords = N'Y'
else
set @AllRecords = N'N'
select MyColumn
from MyTable
where (MyValue = @MyParameter or @AllRecords = N'Y')
Hopefully, I won't ever have to implement a system that mixes flags and data value in this way.
UPDATED
Here is a version that should work with your expanded requirements (this requires one of the newer versions of SQL Server, I think):
declare @SelectedLevels table (LevelId int not null primary key)
if @LevelId = 3
insert into @SelectedLevels (LevelId) values (1), (2)
else if @LevelId = 5
insert into @SelectedLevels (LevelId) values (0), (1), (2)
else
insert into @SelectedLevels (LevelId) values (@LevelId)
select mt.MyColumn
from MyTable mt
inner join @SelectedLevels sl on sl.LevelId = MyTable.LevelId
if @Param = 3
begin
select *
from @T
end
else
if @Param = 2
begin
select *
from @T
where id in (0,1)
end
else
begin
select *
from @T
where id = @Param
end
精彩评论