UpdateText and Table Variable

[schafjoe]

Everybody,

Using the code below, I get the followin error:
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '@retTable'.

DECLARE @TextLine1 VarChar(20),
	@IntCounter Int,
	@Pointer VarBinary(16)

DECLARE @retTable TABLE 
( SelectDetailSubClause Text )

INSERT INTO @retTable (SelectDetailSubClause)
VALUES (NULL)

SET @TextLine1 = 'Line to be inserted.'
SET @IntCounter = 0

WHILE @IntCounter <= 10
BEGIN
SELECT @Pointer = TEXTPTR(RT.SelectDetailSubClause) FROM @retTable RT
UPDATETEXT @retTable.SelectDetailSubClause @Pointer NULL NULL @TextLine1
SET @IntCounter = @IntCounter + 1
END
-- END WHILE

SELECT RT.SelectDetailSubClause FROM @retTable RT

 

[RileyCat]

Should be

Create Table #TableName

# indicates a temporary table and must be dropped at the end of the procedure.

 

[schafjoe]

I have made the following changes to my code.

DECLARE @TextLine1 VarChar(20),
    @IntCounter Int,
    @Pointer VarBinary(16)

CREATE TABLE #retTable TABLE 
( SelectDetailSubClause Text )

INSERT INTO #retTable (SelectDetailSubClause)
VALUES (NULL)

SET @TextLine1 = 'Line to be inserted.'
SET @IntCounter = 0

WHILE @IntCounter <= 10
BEGIN
SELECT @Pointer = TEXTPTR(RT.SelectDetailSubClause) FROM #retTable RT
UPDATETEXT #retTable.SelectDetailSubClause @Pointer NULL NULL @TextLine1
SET @IntCounter = @IntCounter + 1
END
-- END WHILE

SELECT RT.SelectDetailSubClause FROM #retTable RT
DROP TABLE #retTable

However, I now get the following error:
Server: Msg 7133, Level 16, State 2, Line 17
NULL textptr (text, ntext, or image pointer) passed to UpdateText function.

 

[john76]

Not sure why the table variable didn't work... oh well.

Try this:

DECLARE @TextLine1 VarChar(20),
    @IntCounter Int,
    @Pointer VarBinary(16)

CREATE TABLE #retTable
( SelectDetailSubClause Text )

INSERT INTO #retTable (SelectDetailSubClause)
VALUES ([COLOR=red]''[/color])

SET @TextLine1 = 'Line to be inserted.'
SET @IntCounter = 0

WHILE @IntCounter <= 10
BEGIN
SELECT @Pointer = TEXTPTR(RT.SelectDetailSubClause) FROM #retTable RT
UPDATETEXT #retTable.SelectDetailSubClause @Pointer NULL NULL @TextLine1
SET @IntCounter = @IntCounter + 1
END
-- END WHILE

SELECT RT.SelectDetailSubClause FROM #retTable RT
DROP TABLE #retTable

All that I did was initialize the text column to an empty string ('') instead of NULL.

HTH,
John

 

[ESquared]

If the amount of data is small, a table variable is probably better.

You probably just need to change
[tt]@retTable.SelectDetailSubClause[/tt]
to
[tt]RT.SelectDetailSubClause[/tt]

Table variables require you to use aliases.