Data Types

[oddball]

Have the following stored procedure, that crops html content out of the Description Column in my Events Table:

===========================================================

ALTER PROCEDURE GetEvents
(
@ModuleID int
)
AS

SELECT
ItemID,
CreatedByUser,
WhereWhen,
CreatedDate,
Title,
ExpireDate,
Description

INTO #tmptbl
FROM Events
WHERE ModuleID = @ModuleID
AND ExpireDate > GetDate()

While Exists
(Select * From #tmptbl
Where charindex('<',Description)>0
And charindex('>',Description)>0)

Begin

Update #tmptbl
Set Description=stuff(Description, charindex('<',Description),
charindex('>',Description) - charindex('<',Description) + 1, '')
Where charindex('<',Description)>0
And charindex('>',Description)>0

End

SELECT
ItemID,
CreatedByUser,
WhereWhen,
CreatedDate,
Title,
ExpireDate,
LEFT(CAST(Description as nvarchar(4000)), 125) + '...' AS DescriptionShort

From #tmptbl

ORDER BY
ExpireDate

Drop table #tmptbl

===========================================================

Currently my Description column is nvarchar, but i need to increase the data size to cope with 10000+ characters.

Step 4
Options E-Mail Notification Emoticons/Smileys Process TGML
Step 5
Submit Post
This excludes nvarchar & varchar. Have tried ntext, but i get the following error:

===========================================================

Running dbo.&quot;GetEvents&quot;.

(5 row(s) affected)
Argument data type ntext is invalid for argument 1 of stuff function.
(0 row(s) returned)
RETURN_VALUE =
Finished running dbo.&quot;GetEvents&quot;.

===========================================================

What can i do to get around this problem,

cheers,

si

 

[clapag22]

If your datatype is TEXT or NTEXT, certain string functions (including STUFF) do not work. You need to research the UPDATETEXT function in BOL. It's a little tricky at first but you should get the hang of it...

Craig