Looping through temporary table and CASE statements

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>