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

Posted by Jayakumars under ASP.NET AJAX on 12/16/2016 | Points: 10 | Views : 957 | Status : [Member] [MVP] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	hi wait i raise new question for this Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

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

0	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

0	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

0	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

Latest Posts