Nick67
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.[Inspe ctorID] = 7 OR dbo.tblInsDetails.[Inspect orID] = 13 OR dbo.tblInsDetails.[Inspect orID] = 24 OR dbo.tblInsDetails.[Inspect orID] = 37) Or
(dbo.tblInsDetails.[Inspec torID2] = 7 OR dbo.tblInsDetails.[Inspect orID2] = 13 OR dbo.tblInsDetails.[Inspect orID2] = 24 OR dbo.tblInsDetails.[Inspect orID2] = 37) or
(dbo.tblInsDetails.[Inspec torID3] = 7 OR dbo.tblInsDetails.[Inspect orID3] = 13 OR dbo.tblInsDetails.[Inspect orID3] = 24 OR dbo.tblInsDetails.[Inspect orID3] = 37) or
(dbo.tblInsDetails.[Inspec torID4] = 7 OR dbo.tblInsDetails.[Inspect orID4] = 13 OR dbo.tblInsDetails.[Inspect orID4] = 24 OR dbo.tblInsDetails.[Inspect orID4] = 37) or
(dbo.tblInsDetails.[Inspec torID5] = 7 OR dbo.tblInsDetails.[Inspect orID5] = 13 OR dbo.tblInsDetails.[Inspect orID5] = 24 OR dbo.tblInsDetails.[Inspect orID5] = 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?
case when
((dbo.tblInsDetails.[Inspe
(dbo.tblInsDetails.[Inspec
(dbo.tblInsDetails.[Inspec
(dbo.tblInsDetails.[Inspec
(dbo.tblInsDetails.[Inspec
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.[Inspe ctorID] in (select distinct myCol from mySimpleTable)
Or ...
that way when you need people come & go you can just adjust this table.
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.[Inspe
Or ...
that way when you need people come & go you can just adjust this table.
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.[Inspec torID] IN (7, 13, 24, 37) And>
for
<(dbo.tblInsDetails.[Inspe ctorID] = 7 OR dbo.tblInsDetails.[Inspect orID] = 13 OR dbo.tblInsDetails.[Inspect orID] = 24 OR dbo.tblInsDetails.[Inspect orID] = 37) and>
does not result in good syntax that I can use to alter the sproc
for
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.[Inspec
for
<(dbo.tblInsDetails.[Inspe
does not result in good syntax that I can use to alter the sproc
for
ASKER
Ah, my bad.
Bracketing...needful, but painful.
Now can I replace
<dbo.tblInsDetails.[Inspec torID] IN (7, 13, 24, 37) >
with
dbo.tblInsDetails.[Inspect orID] IN (Select InspectorID from @Engineers)
Bracketing...needful, but painful.
Now can I replace
<dbo.tblInsDetails.[Inspec
with
dbo.tblInsDetails.[Inspect
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My comment shows the final code that was put into production and how the question was ultimately sloved
ASKER
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