August 12, 2021 at 2:15 pm
I have the following statement in my stored procedure
SELECT
mpe.MeasuringPointEntryID
FROM
[location] l
JOIN
MeasuringPoint MP ON l.LocationID = mp.LocationID
JOIN
MeasuringPointEntry MPE ON mp.MeasuringPointID = MPE.MeasuringPointID
JOIN
MeasuringPointEntryChannel MPEC ON MPE.MeasuringPointEntryID = MPEC.MeasuringPointEntryID
WHERE
mpe.SerialNumber = @SerialNumber
AND
mpec.ChannelID = @ChannelID
AND
(
(mpe.DateTimeEnd IS NOT NULL AND @AnnotationDate BETWEEN mpe.DateTimeStart and mpe.DateTimeEnd)
OR
(mpe.DateTimeEnd IS NULL AND @AnnotationDate >= mpe.DateTimeStart)
Now I want to modify this by sending in a variable
DECLARE @AlarmNotification BIT =1
So that i only execute as follows:
mpe.SerialNumber = @SerialNumber
AND
mpec.ChannelID = @ChannelID
and
mpe.DateTimeEnd is not null
2) @AlarmNotification = 0 then the where clause is
mpe.SerialNumber = @SerialNumber
AND
mpec.ChannelID = @ChannelID
(
(mpe.DateTimeEnd IS NOT NULL AND @AnnotationDate BETWEEN mpe.DateTimeStart and mpe.DateTimeEnd)
OR
(mpe.DateTimeEnd IS NULL AND @AnnotationDate >= mpe.DateTimeStart)
hows is the best way to this please?
i tried the following but this did not work:
WHERE
Case when @AlarmNotification = 1 then
mpe.SerialNumber = @SerialNumber
AND
mpec.ChannelID = @ChannelID
case when @AlarmNotification = 0 then
mpe.SerialNumber = @SerialNumber
AND
mpec.ChannelID = @ChannelID
(
(mpe.DateTimeEnd IS NOT NULL AND @AnnotationDate BETWEEN mpe.DateTimeStart and mpe.DateTimeEnd)
OR
(mpe.DateTimeEnd IS NULL AND @AnnotationDate >= mpe.DateTimeStart)
else
end
August 12, 2021 at 7:23 pm
Uneducated guess:
DECLARE @AlarmNotification BIT
SELECT
mpe.MeasuringPointEntryID,
@AlarmNotification = CASE WHEN mpe.DateTimeEnd IS NOT NULL THEN 1 ELSE 0 END
FROM [location] l
JOIN MeasuringPoint MP ON l.LocationID = mp.LocationID
JOIN MeasuringPointEntry MPE ON mp.MeasuringPointID = MPE.MeasuringPointID
JOIN MeasuringPointEntryChannel MPEC ON MPE.MeasuringPointEntryID = MPEC.MeasuringPointEntryID
WHERE
mpe.SerialNumber = @SerialNumber
AND mpec.ChannelID = @ChannelID
AND (
(mpe.DateTimeEnd IS NOT NULL AND @AnnotationDate BETWEEN mpe.DateTimeStart and mpe.DateTimeEnd)
OR
(mpe.DateTimeEnd IS NULL AND @AnnotationDate >= mpe.DateTimeStart)
)
August 12, 2021 at 7:36 pm
If I misunderstood and you want the alarm noticiation flag to control the selection criteria, then it's best to use two separate queries instead of trying to cram everything into one query, i.e. something along these lines:
IF @AlarmNotification = 1 BEGIN
query one
END
ELSE BEGIN
query two
END
August 12, 2021 at 8:05 pm
If I misunderstood and you want the alarm noticiation flag to control the selection criteria, then it's best to use two separate queries instead of trying to cram everything into one query, i.e. something along these lines:
IF @AlarmNotification = 1 BEGIN
query one
END
ELSE BEGIN
query two
END
Or, better yet, three stored procs.
The "parent" proc tests the values of the parameters, and calls the appropriate procedure.
Ultimately, you want you code to do one thing the same way every time.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply