asked on

Replace a block of conditions

I have added another staff member, and this block of condtionals is begining to get unwieldy

case when
((dbo.tblInsDetails.[InspectorID] = 7 OR dbo.tblInsDetails.[InspectorID] = 13 OR dbo.tblInsDetails.[InspectorID] = 24 OR dbo.tblInsDetails.[InspectorID] = 37) Or
(dbo.tblInsDetails.[InspectorID2] = 7 OR dbo.tblInsDetails.[InspectorID2] = 13 OR dbo.tblInsDetails.[InspectorID2] = 24 OR dbo.tblInsDetails.[InspectorID2] = 37) or
(dbo.tblInsDetails.[InspectorID3] = 7 OR dbo.tblInsDetails.[InspectorID3] = 13 OR dbo.tblInsDetails.[InspectorID3] = 24 OR dbo.tblInsDetails.[InspectorID3] = 37) or
(dbo.tblInsDetails.[InspectorID4] = 7 OR dbo.tblInsDetails.[InspectorID4] = 13 OR dbo.tblInsDetails.[InspectorID4] = 24 OR dbo.tblInsDetails.[InspectorID4] = 37) or
(dbo.tblInsDetails.[InspectorID5] = 7 OR dbo.tblInsDetails.[InspectorID5] = 13 OR dbo.tblInsDetails.[InspectorID5] = 24 OR dbo.tblInsDetails.[InspectorID5] = 37)) then 1
else 0
end) as SomeColumnIReturn

Now, it is easy enough for me to get these four dudes InspectorID's and zero into a table variable

-- a table variable to hold the values
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]

What I am having trouble with is constructing the boolean to replace that big block of hard-coded ID comparisons
Pseudo-coded, I want
case when
(InspectorID in (select InspectorID from @Engineers) or
InspectorID2 in (select InspectorID from @Engineers) or
InspectorID3 in (select InspectorID from @Engineers) or
InspectorID4 in (select InspectorID from @Engineers) or
InspectorID5 in (select InspectorID from @Engineers) )
then 1
else 0
end
) as SomeColumnIReturn

It just doesn't play.
Ideas?

SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick67

ASKER

That still means if, lets say 37 goes inactive and 42 is hired to replace him that I will need to hand edit this procedure.
New hires get added to the table, and people that leave get marked inacative.

I'd like to alter this procedure once, and then not have to hand-edit every time someone is hired or quits

Ephraim Wangoya

Then you should pass the IDs as a delimited parameter to your stored procedure.
In you SP pass the parameter into a temporary table and use the values from there

knightEknight

You can add these values to a simple table like this:

insert into mySimpleTable select 7
insert into mySimpleTable select 13
insert into mySimpleTable select 24
insert into mySimpleTable select 37

then in your query do this:

((dbo.tblInsDetails.[InspectorID] in (select distinct myCol from mySimpleTable)
Or ...

that way when you need people come & go you can just adjust this table.

Nick67

ASKER

I could pass the ID's as a parameters to the sproc.
That still means getting the ID's and passing them in -- and I have no trouble getting them into a table variable as it is
This part works just fine

-- a table variable to hold the values
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]

If I Issue

Select InspectorID from@ Engineers,
it returns 7,13,24,37 and 0 just like I want.

The case boolean goes BANG!
Trying to substitute
<dbo.tblInsDetails.[InspectorID] IN (7, 13, 24, 37) And>
for
<(dbo.tblInsDetails.[InspectorID] = 7 OR dbo.tblInsDetails.[InspectorID] = 13 OR dbo.tblInsDetails.[InspectorID] = 24 OR dbo.tblInsDetails.[InspectorID] = 37) and>

does not result in good syntax that I can use to alter the sproc

for

Nick67

ASKER

Ah, my bad.
Bracketing...needful, but painful.

Now can I replace
<dbo.tblInsDetails.[InspectorID] IN (7, 13, 24, 37) >
with
dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers)

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick67

ASKER

My comment shows the final code that was put into production and how the question was ultimately sloved