Track which condition was true in a stored procedure

I have the following stored procedure:

ALTER PROCEDURE [dbo].[GetRepeatIssues]
@thirty datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Repeat varchar(50);
SELECT
e.first_name,e.last_name,db1.number,db1.date,db2.number,db2. date from
dashboard db1, dashboard db2, employees e where
(db1.date > @thirty OR db2.date > @thirty) AND
e.employee_id=db1.employee and
db1.employee=db2.employee and
db1.number <> db2.number and
db1.date <> db2.date and
db1.date<=db2.date and
(db1.date + 30) >= db2.date and
(
(db1.live_1 = '2' and db2.live_1 = '2') or
(db1.live_2 = '2' and db2.live_2 = '2') or
(db1.live_3 = '2' and db2.live_3 = '2') or
(db1.live_4 = '2' and db2.live_4 = '2') or
(db1.live_5 = '2' and db2.live_5 = '2') or
(db1.live_6 = '2' and db2.live_6 = '2') or
(db1.live_7 = '2' and db2.live_7 = '2') or
(db1.live_8 = '2' and db2.live_8 = '2') or
(db1.review_1 = '2' and db2.review_1 = '2') or
(db1.review_2 = '2' and db2.review_2 = '2') or
(db1.review_3 = '2' and db2.review_3 = '2') or
(db1.review_4 = '2' and db2.review_4 = '2') or
(db1.review_5 = '2' and db2.review_5 = '2') or
(db1.review_6 = '2' and db2.review_6 = '2') or
(db1.review_7 = '2' and db2.review_7 = '2') or
(db1.review_8 = '2' and db2.review_8 = '2') or
(db1.review_9 = '2' and db2.review_9 = '2') or
(db1.review_10 = '2' and db2.review_10 = '2') or
(db1.review_11 = '2' and db2.review_11 = '2') or
(db1.review_12 = '2' and db2.review_12 = '2')
)
ORDER BY db2.date DESC;
END

I am trying to find a way to track which one of the conditions
(db1.blah = '2' and db2.blah = '2') is coming true when the procedure
runs, so I can provide more data than just the fact that "there is an
issue".

Anyone have any ideas?

Re: Track which condition was true in a stored procedure

On Sep 13, 12:36 pm, Nate wrote:
> I have the following stored procedure:
>
> ALTER PROCEDURE [dbo].[GetRepeatIssues]
> @thirty datetime
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @Repeat varchar(50);
> SELECT
> e.first_name,e.last_name,db1.number,db1.date,db2.number,db2. date from
> dashboard db1, dashboard db2, employees e where
> (db1.date > @thirty OR db2.date > @thirty) AND
> e.employee_id=db1.employee and
> db1.employee=db2.employee and
> db1.number <> db2.number and
> db1.date <> db2.date and
> db1.date<=db2.date and
> (db1.date + 30) >= db2.date and
> (
> (db1.live_1 = '2' and db2.live_1 = '2') or
> (db1.live_2 = '2' and db2.live_2 = '2') or
> (db1.live_3 = '2' and db2.live_3 = '2') or
> (db1.live_4 = '2' and db2.live_4 = '2') or
> (db1.live_5 = '2' and db2.live_5 = '2') or
> (db1.live_6 = '2' and db2.live_6 = '2') or
> (db1.live_7 = '2' and db2.live_7 = '2') or
> (db1.live_8 = '2' and db2.live_8 = '2') or
> (db1.review_1 = '2' and db2.review_1 = '2') or
> (db1.review_2 = '2' and db2.review_2 = '2') or
> (db1.review_3 = '2' and db2.review_3 = '2') or
> (db1.review_4 = '2' and db2.review_4 = '2') or
> (db1.review_5 = '2' and db2.review_5 = '2') or
> (db1.review_6 = '2' and db2.review_6 = '2') or
> (db1.review_7 = '2' and db2.review_7 = '2') or
> (db1.review_8 = '2' and db2.review_8 = '2') or
> (db1.review_9 = '2' and db2.review_9 = '2') or
> (db1.review_10 = '2' and db2.review_10 = '2') or
> (db1.review_11 = '2' and db2.review_11 = '2') or
> (db1.review_12 = '2' and db2.review_12 = '2')
> )
> ORDER BY db2.date DESC;
> END
>
> I am trying to find a way to track which one of the conditions
> (db1.blah = '2' and db2.blah = '2') is coming true when the procedure
> runs, so I can provide more data than just the fact that "there is an
> issue".
>
> Anyone have any ideas?

Add each of those conditions to your select list as a case statement:

case when db1.live_1 = '2' and db2.live_1 = '2' then 1 else 0 end
cond1,
case when db1.live_2 = '2' and db2.live_2 = '2' then 1 else 0 end
cond2,
....

Re: Track which condition was true in a stored procedure

EXCELLENT! Thank you, Zeldor.


On Sep 13, 1:55 pm, ZeldorBlat wrote:
> On Sep 13, 12:36 pm, Nate wrote:
>
>
>
>
>
> > I have the following stored procedure:
>
> > ALTER PROCEDURE [dbo].[GetRepeatIssues]
> > @thirty datetime
> > AS
> > BEGIN
> > SET NOCOUNT ON;
> > DECLARE @Repeat varchar(50);
> > SELECT
> > e.first_name,e.last_name,db1.number,db1.date,db2.number,db2. date from
> > dashboard db1, dashboard db2, employees e where
> > (db1.date > @thirty OR db2.date > @thirty) AND
> > e.employee_id=db1.employee and
> > db1.employee=db2.employee and
> > db1.number <> db2.number and
> > db1.date <> db2.date and
> > db1.date<=db2.date and
> > (db1.date + 30) >= db2.date and
> > (
> > (db1.live_1 = '2' and db2.live_1 = '2') or
> > (db1.live_2 = '2' and db2.live_2 = '2') or
> > (db1.live_3 = '2' and db2.live_3 = '2') or
> > (db1.live_4 = '2' and db2.live_4 = '2') or
> > (db1.live_5 = '2' and db2.live_5 = '2') or
> > (db1.live_6 = '2' and db2.live_6 = '2') or
> > (db1.live_7 = '2' and db2.live_7 = '2') or
> > (db1.live_8 = '2' and db2.live_8 = '2') or
> > (db1.review_1 = '2' and db2.review_1 = '2') or
> > (db1.review_2 = '2' and db2.review_2 = '2') or
> > (db1.review_3 = '2' and db2.review_3 = '2') or
> > (db1.review_4 = '2' and db2.review_4 = '2') or
> > (db1.review_5 = '2' and db2.review_5 = '2') or
> > (db1.review_6 = '2' and db2.review_6 = '2') or
> > (db1.review_7 = '2' and db2.review_7 = '2') or
> > (db1.review_8 = '2' and db2.review_8 = '2') or
> > (db1.review_9 = '2' and db2.review_9 = '2') or
> > (db1.review_10 = '2' and db2.review_10 = '2') or
> > (db1.review_11 = '2' and db2.review_11 = '2') or
> > (db1.review_12 = '2' and db2.review_12 = '2')
> > )
> > ORDER BY db2.date DESC;
> > END
>
> > I am trying to find a way to track which one of the conditions
> > (db1.blah = '2' and db2.blah = '2') is coming true when the procedure
> > runs, so I can provide more data than just the fact that "there is an
> > issue".
>
> > Anyone have any ideas?
>
> Add each of those conditions to your select list as a case statement:
>
> case when db1.live_1 = '2' and db2.live_1 = '2' then 1 else 0 end
> cond1,
> case when db1.live_2 = '2' and db2.live_2 = '2' then 1 else 0 end
> cond2,
> ...- Hide quoted text -
>
> - Show quoted text -

Re: Track which condition was true in a stored procedure

On Sep 13, 11:36 am, Nate wrote:
> I have the following stored procedure:
>
> ALTER PROCEDURE [dbo].[GetRepeatIssues]
> @thirty datetime
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @Repeat varchar(50);
> SELECT
> e.first_name,e.last_name,db1.number,db1.date,db2.number,db2. date from
> dashboard db1, dashboard db2, employees e where
> (db1.date > @thirty OR db2.date > @thirty) AND
> e.employee_id=db1.employee and
> db1.employee=db2.employee and
> db1.number <> db2.number and
> db1.date <> db2.date and
> db1.date<=db2.date and
> (db1.date + 30) >= db2.date and
> (
> (db1.live_1 = '2' and db2.live_1 = '2') or
> (db1.live_2 = '2' and db2.live_2 = '2') or
> (db1.live_3 = '2' and db2.live_3 = '2') or
> (db1.live_4 = '2' and db2.live_4 = '2') or
> (db1.live_5 = '2' and db2.live_5 = '2') or
> (db1.live_6 = '2' and db2.live_6 = '2') or
> (db1.live_7 = '2' and db2.live_7 = '2') or
> (db1.live_8 = '2' and db2.live_8 = '2') or
> (db1.review_1 = '2' and db2.review_1 = '2') or
> (db1.review_2 = '2' and db2.review_2 = '2') or
> (db1.review_3 = '2' and db2.review_3 = '2') or
> (db1.review_4 = '2' and db2.review_4 = '2') or
> (db1.review_5 = '2' and db2.review_5 = '2') or
> (db1.review_6 = '2' and db2.review_6 = '2') or
> (db1.review_7 = '2' and db2.review_7 = '2') or
> (db1.review_8 = '2' and db2.review_8 = '2') or
> (db1.review_9 = '2' and db2.review_9 = '2') or
> (db1.review_10 = '2' and db2.review_10 = '2') or
> (db1.review_11 = '2' and db2.review_11 = '2') or
> (db1.review_12 = '2' and db2.review_12 = '2')
> )
> ORDER BY db2.date DESC;
> END
>
> I am trying to find a way to track which one of the conditions
> (db1.blah = '2' and db2.blah = '2') is coming true when the procedure
> runs, so I can provide more data than just the fact that "there is an
> issue".
>
> Anyone have any ideas?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

What you did post has vague reserved words for data element names
(employee_, _date, _number, etc.)
It also looks like you have a non-1NF table, assuming that those
numbered columns are repeated groups.

In general, a transition history ought to have columns for a prior and
a current status with the appropriate temporal stamps. You are
mimicking a clipboard wher you write down a list in chronological
order and not creating a proper table at all. Then the bad design
leads you to trying to get the right structure in a query with all the
needless overhead.

Let's start over with some specs, please.