How to Solve this Query

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

How to solve this

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	You can try with the below query CREATE PROC test_check1(@id INT) AS BEGIN SELECT * FROM TABLERECS1 WHERE 1= (CASE WHEN @id = 1001 AND Empstatus NOT IN ('CLO','ACP') THEN 1 WHEN @id != 1001 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

You can try with the below query

CREATE PROC test_check1(@id INT)
AS
BEGIN
SELECT * FROM TABLERECS1
WHERE 1= (CASE 
              WHEN @id = 1001 AND Empstatus NOT IN ('CLO','ACP') THEN 1
              WHEN @id != 1001 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

Latest Posts