Hello Expert !
I need your help to make SP to work on if Else condition . I have one column name which is not added in below update query which is Hours. I need that if Hours > 0 then run first update query other wise go for second query.
Thanks you in advance !
Code:
CREATE PROCEDURE [dbo].[TESTSP_IF_ELSE]
(
@StartDate as datetime= ‘19000101’,
@EndDate as datetime= ‘90000101’
)
AS
Begin
SET @StartDate= CASE @StartDate WHEN ‘19000101’ THEN DATEDIFF(dd,0,GetDate()-2) ELSE @StartDate END;
Set @EndDate = CASE @EndDate When ‘90000101’ THEN DateAdd(dd, DateDiff(dd,0,GetDate()), 0) ELSE @EndDate End;
UPDATE ITEM_EVENT SET VAL25 =
(SELECT
CASE (EVT.VAL1) WHEN Null THEN (0) ELSE CASE (EVT.VAL1) WHEN 0 THEN (0)ELSE CASE (EVT.VAL1) WHEN 0 then (0 )ELSE Round(EVT.VAL1*304.8*1.66257142857143*6.28981057,2) END END END
FROM ITEM_EVENT EVT,ITEM ID,ITEM_PROPERTY ip
WHERE EVT.EVENT_TYPE =’TYPE1′ AND ID.ITEM_ID=EVT.ITEM_ID AND ID.ITEM_ID=ip.ITEM_ID
AND ID.ITEM_TYPE=’TYPEZ’ AND ip.PROPERTY_STRING IN
(‘FSKA’)
AND ITEM_EVENT.START_DATETIME=EVT.START_DATETIME
)-
(SELECT
CASE (EVT.VAL1) WHEN Null THEN (0) ELSE CASE (EVT.VAL1) WHEN 0 THEN (0)ELSE CASE (EVT.VAL1) WHEN 0 then (0 )ELSE Round(EVT.VAL1*304.8*1.66257142857143*6.28981057,2) END END END
FROM ITEM_EVENT EVT,ITEM ID,ITEM_PROPERTY ip
WHERE EVT.EVENT_TYPE =’TYPEA’ AND ID.ITEM_ID=EVT.ITEM_ID AND ID.ITEM_ID=ip.ITEM_ID
AND ID.ITEM_TYPE=’TYPEZ’ AND ip.PROPERTY_STRING IN
(‘FSKA’)
AND ITEM_EVENT.START_DATETIME=EVT.START_DATETIME +1
)
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_PROPERTY WHERE PROPERTY_STRING =’FSKA’)
AND ITEM_EVENT.START_DATETIME>= @StartDate
AND ITEM_EVENT.START_DATETIME= @StartDate
AND ITEM_EVENT.START_DATETIME<= @EndDate
AND ITEM_EVENT.EVENT_TYPE ='TYPEB' END
