How to set case statement in after where statment [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 12/16/2016 | Points: 10 | Views : 410 | Status : [Member] [MVP] | Replies : 7
CREATE TABLE TABLERECS
(
id int primary key identity(1,1),
Empname nvarchar(20),
Empstatus nvarchar(20)
)


insert into TABLERECS values('John','ACT')
insert into TABLERECS values('Rob John','PEN')
insert into TABLERECS values('Little John','CAN')
insert into TABLERECS values('Flour John','CLO')
insert into TABLERECS values('rich John','TER')
insert into TABLERECS values('save John','ACP')


SELECT * FROM TABLERECS

create proc Test.dbo.test_check(@id int)
as
begin
select * from TABLERECS
where Empstatus IN (SELECT DISTINCT Empstatus from TABLERECS)
end


exec test_check 1

/** i tried my self but i think this is wrong . any one guide me.

/** 1. I write my Query above but when i pass id=1 i need show all records (all Status records)
/** 2. When pass greater then 1 ex : id=2 or 3 or 4 or 5 etc any one
/** I need show all reocrds ( but only Status PEN,CAN only
//* but i need single query no need if statment
//* i think we need case statement in the Empstatus . any one guide me

Mark as Answer if its helpful to you


Responses

Posted by: A2H on: 12/16/2016 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
You can try with the below query. Change your where condition like given below

ALTER PROC test_check(@id INT)
AS
BEGIN
SELECT * FROM TABLERECS
WHERE 1= (CASE
WHEN @id = 1 THEN 1
WHEN @id != 1 AND Empstatus IN ('PEN','CAN')THEN 1
END)
END


Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 12/16/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

little bit altered

when i pass 1 here all records shows but i need when i pass id =1

i need all records shown but not equal CLO

Empstatus <>'CLO' when i pass 1

other condition running good .

can you update this?



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 12/16/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
wait i raise new question for this

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 12/16/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
CREATE TABLE TABLERECS1

(
id int primary key identity(1,1),
Empname nvarchar(20),
Empstatus nvarchar(20),
EmpAssignID INT
)


insert into TABLERECS1 values('John','ACT',1001)
insert into TABLERECS1 values('Rob John','PEN',1001)
insert into TABLERECS1 values('Little John','CAN',1001)
insert into TABLERECS1 values('Flour John','CLO',1001)
insert into TABLERECS1 values('rich John','TER',1001)
insert into TABLERECS1 values('save John','ACP',1001)



SELECT * FROM TABLERECS1


/** I need how to create procedure following Criteria
/** 1. When i pass EmpAssignID=1001 i need show all records (all Status records. But Not Equal <>'CLO' AND 'ACP') those records only
/** 2. When pass id<>1001 I need show all reocrds ( but only Status PEN,CAN only)
//* but i need single query no need if statment
//* i think we need case statement in the Empstatus . any one guide me

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 12/16/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
I have responded to the other thread. Please check that one for the answer

Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lydialoftis on: 12/20/2016 [Member] Starter | Points: 25

Up
0
Down
I think you can solve this issue if you make some changes in your Where condition.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ednabaron on: 12/21/2016 [Member] Starter | Points: 25

Up
0
Down
There is a minute mistake in your Where condition. You have to make necessary changes in it.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response