January 4, 2012 at 10:45 pm
well its a bit confusing to say like i want to enable a condition based on a specific condition..
explaining this , my front end has an employee ID dropdown list with options --All--,rest of the emp Ids
when --All-- is selected, all the employees should be displayed and i want this in a single query
if exists (select top 1 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='test_data')
drop table test_data
create table test_data(Ename nvarchar(max),Eid int, Esalary int)
insert into test_data(Ename,Eid,Esalary) values('me',1,100)
insert into test_data(Ename,Eid,Esalary) values('you',2,300)
insert into test_data(Ename,Eid,Esalary) values('x',3,350)
insert into test_data(Ename,Eid,Esalary) values('ab',4,450)
insert into test_data(Ename,Eid,Esalary) values('abc',41,4500)
--this doesn't work
select * from test_data where Eid in case when @param='%' then Eid in (select Eid from test_data) else Eid end
-- managing currently like this
select * from test_data where Eid like @param -- gives all the values if @param='%' if @param=3 only specific value.
drop table test_data
is there any other way around to do this..?
January 5, 2012 at 1:46 am
I think you're looking at catch all queries.
Try this blog post by Gail[/url]
January 5, 2012 at 2:23 am
Your selection logic is flawed. You should read Gail's article, but I think that this would be closer to what you need:
select * from test_data
where Eid = (case when @param='%' then Eid else @param end)
No doubt you already know this, but using select * is generally considered bad practice.
January 5, 2012 at 3:53 am
thanks Phil, i do know the implications of *..
January 5, 2012 at 4:04 am
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply