#tmp table within WHEN statement

Question

Hi

This is how i created my required temp table in a query window..

CREATE TABLE #Tmp
 ( CtrlAlarmMsgID int,
  Alarm nvarchar(50)
  );
 INSERT INTO #Tmp (CtrlAlarmMsgID,Alarm)
 select CtrlAlarmMsgID,SUBSTRING(Alarm,21,99) AS Alarm from ContAlarmLog where Controllerid = 28305 Order by CreatedOn DESC;
 Select * from #Tmp as T;
 DROP TABLE #Tmp;

but when i am trying to add this into my Stored procedure requirement its giving me error. Following is the code..

SELECT a.ContAlarmCritID, c.CtrlAlarmMsgID,
 CASE 
	  WHEN 
		 (SELECT TOP 1 SUBSTRING(Alarm,21,99) from ContAlarmLog where Controllerid = 28305 AND CtrlAlarmMsgID =  c.CtrlAlarmMsgID AND Alarm IS NOT NULL ORDER BY 
					ContAlarmLogId desc) IS NULL 
	  THEN  dbo.N360_fn_NEC_Get_NCM_Analog_Name(c.AlarmName_Short,a.ControllerID,c.ParameterID,1,6) 
	  ELSE	
		  BEGIN		  
			 CREATE TABLE #Tmp
			 ( CtrlAlarmMsgID int,
			   ContAlarmLogId int,				 
				Alarm nvarchar(50)
			 );
			 INSERT INTO #Tmp (CtrlAlarmMsgID,ContAlarmLogId,Alarm)
			 SELECT CtrlAlarmMsgID,ContAlarmLogId,SUBSTRING(Alarm,21,99) AS Alarm from ContAlarmLog where Controllerid = 28305 Order by CreatedOn DESC;
				 
			 SELECT TOP 1 SortedContAlarmLog.Alarm from #Tmp As SortedContAlarmLog where SortedContAlarmLog.CtrlAlarmMsgID = c.CtrlAlarmMsgID AND SortedContAlarmLog.Alarm IS NOT NULL ORDER BY SortedContAlarmLog.ContAlarmLogId DESC;
			 DROP TABLE #Tmp;
		 END
END AS 'AlarmName'					
			 
FROM ControllerAlarmCritical a       
		 INNER JOIN ControllerAlarmMapping b ON a.CtrlAlarmMsgID = b.CtrlAlarmMsgID      
		 INNER JOIN ControllerAlarmMessages c ON a.CtrlAlarmMsgID = c.CtrlAlarmMsgID  
		 WHERE c.IsAlarmOn = 1 AND b.ConTechID = 6 AND a.ControllerID = 28305     
		 ORDER BY alarmname 

But i am receiving following error...

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'BEGIN'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'AS'.

   Please let me know where i am wrong

Answer 1

Hi,

In CASE ELSE Statement we cannot use BEGIN KeyWord.

try by removing BEGIN END in CASE ELSE part

SELECT a.ContAlarmCritID, c.CtrlAlarmMsgID,
CASE 
  WHEN 
(SELECT TOP 1 SUBSTRING(Alarm,21,99) from ContAlarmLog where Controllerid = 28305 AND CtrlAlarmMsgID =  c.CtrlAlarmMsgID AND Alarm IS NOT NULL ORDER BY 
ContAlarmLogId desc) IS NULL 
  THEN  dbo.N360_fn_NEC_Get_NCM_Analog_Name(c.AlarmName_Short,a.ControllerID,c.ParameterID,1,6) 
  ELSE 
   
CREATE TABLE #Tmp
( CtrlAlarmMsgID int,
   ContAlarmLogId int, 
Alarm nvarchar(50)
);
INSERT INTO #Tmp (CtrlAlarmMsgID,ContAlarmLogId,Alarm)
SELECT CtrlAlarmMsgID,ContAlarmLogId,SUBSTRING(Alarm,21,99) AS Alarm from ContAlarmLog where Controllerid = 28305 Order by CreatedOn DESC;
SELECT TOP 1 SortedContAlarmLog.Alarm from #Tmp As SortedContAlarmLog where SortedContAlarmLog.CtrlAlarmMsgID = c.CtrlAlarmMsgID AND SortedContAlarmLog.Alarm IS NOT NULL ORDER BY SortedContAlarmLog.ContAlarmLogId DESC;
DROP TABLE #Tmp;
END 
FROM ControllerAlarmCritical a       
INNER JOIN ControllerAlarmMapping b ON a.CtrlAlarmMsgID = b.CtrlAlarmMsgID      
INNER JOIN ControllerAlarmMessages c ON a.CtrlAlarmMsgID = c.CtrlAlarmMsgID  
WHERE c.IsAlarmOn = 1 AND b.ConTechID = 6 AND a.ControllerID = 28305     
ORDER BY alarmname 

---

PS.Shakeer Hussain

Answer 2

Hi

Syntax for using CASE Statement

SELECT col1, CASE col2 
 WHEN 'M' THEN 'MMM'
ELSE 'XYZ'
END,col3 FROM TableName

---

PS.Shakeer Hussain

Answer 3

Thanks for you reply. I tried your query too but its giving me error as follows..

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'CREATE'.
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'FROM'.

I got some information as follows ,

you cant create a temp table or variable in case statement..

you can only refer them..

Need to work on these. 

Answer 4

Hi,

I think we can't CREATE a Table in CASE Statement. What is the paroblem if you create it outside the CASE Statement?

CREATE TABLE #Tmp
( CtrlAlarmMsgID int,
   ContAlarmLogId int, 
Alarm nvarchar(50)
);
SELECT a.ContAlarmCritID, c.CtrlAlarmMsgID,
CASE 
  WHEN 
(SELECT TOP 1 SUBSTRING(Alarm,21,99) from ContAlarmLog where Controllerid = 28305 AND CtrlAlarmMsgID =  c.CtrlAlarmMsgID AND Alarm IS NOT NULL ORDER BY ContAlarmLogId desc) IS NULL 
  THEN  dbo.N360_fn_NEC_Get_NCM_Analog_Name(c.AlarmName_Short,a.ControllerID,c.ParameterID,1,6) 
  ELSE 
   
INSERT INTO #Tmp (CtrlAlarmMsgID,ContAlarmLogId,Alarm)
SELECT CtrlAlarmMsgID,ContAlarmLogId,SUBSTRING(Alarm,21,99) AS Alarm from ContAlarmLog where Controllerid = 28305 Order by CreatedOn DESC;
SELECT TOP 1 SortedContAlarmLog.Alarm from #Tmp As SortedContAlarmLog where SortedContAlarmLog.CtrlAlarmMsgID = c.CtrlAlarmMsgID AND SortedContAlarmLog.Alarm IS NOT NULL ORDER BY SortedContAlarmLog.ContAlarmLogId DESC;
DROP TABLE #Tmp 
END 
FROM ControllerAlarmCritical a       
INNER JOIN ControllerAlarmMapping b ON a.CtrlAlarmMsgID = b.CtrlAlarmMsgID      
INNER JOIN ControllerAlarmMessages c ON a.CtrlAlarmMsgID = c.CtrlAlarmMsgID  
WHERE c.IsAlarmOn = 1 AND b.ConTechID = 6 AND a.ControllerID = 28305     
ORDER BY alarmname 

---

PS.Shakeer Hussain