Below is a nice stored procedure written to demonstrate looping through a temporary table and using the CASE statement.
Hope this helps.
Ishan
<pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;background-image:URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg8j6CvyCjQOs5FbK6wq5m1sqYLT9_LTBPyeVOoHj6TzkggyesWA5YsBav67dqWRLGY24VJqSiSFmkt99TTg79vfwDwWdmSywnWEW4stFYhHpIhcdQJnxdNjvnIIlSTMFfkV9OIj3I-s_e/s320/codebg.gif);padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> -- Insert statements for procedure here CREATE TABLE #temp_tblMessage ( nId INT IDENTITY(1,1), aEventId INT, nGlobalCaseId INT, mMsgContent NTEXT, mMsgHTMLContent NTEXT, dCDServerTime DATETIME, nType INT, nMediaType INT, nStatus INT, ) INSERT #temp_tblMessage SELECT aEventID, nGlobalCaseID, mMsgContent, mMsgHTMLContent, dCDServerTime, nType, nMediaType, nStatus FROM dbo.tblMessage WHERE nGlobalCaseId = @nInteractionId AND (nType = 32 OR nType = 64 or nType = 96) DECLARE @tFullMessage NVARCHAR(MAX) DECLARE @tHTMLMessageContent NVARCHAR(MAX) DECLARE @tPlainMessageContent NVARCHAR(MAX) DECLARE @tMessage NVARCHAR(MAX) DECLARE @tMessagePrefix NVARCHAR(MAX) DECLARE @dMessageDatetime DATETIME DECLARE @nType INT DECLARE @nCount INT DECLARE @nIndex INT SET @tFullMessage = '' SET @nIndex = 0 SELECT @nCount = Count(nId) From #temp_tblMessage WHILE (@nCount > @nIndex) BEGIN SET @nIndex = @nIndex + 1 SELECT @dMessageDatetime = dCDServerTime, @tPlainMessageContent=mMsgContent, @tHTMLMessageContent = mMsgHTMLContent, @tMessagePrefix = CASE nType WHEN 32 THEN 'Message sent by you at : ' WHEN 64 THEN 'Message from System at : ' WHEN 96 THEN 'Message from System at : ' END FROM #temp_tblMessage WHERE nId = @nIndex SET @tMessagePrefix = @tMessagePrefix + CAST(@dMessageDatetime AS NVARCHAR(255)) + CHAR(13) SET @tMessage = ISNULL(NULLIF(@tPlainMessageContent, ''), @tHTMLMessageContent) SET @tMessage = @tMessagePrefix + @tMessage SET @tFullMessage = @tFullMessage + @tMessage + CHAR(13) END SET @tMessageContent = @tFullMessage </code></pre> 