June 18, 2005 at 11:58 am
Im trying to work out a way to get UPDATETEXT to accept a variable table.column. Unfortunately
UPDATETEXT table.column @TextPointer @OffSet @DeleteLength @ReplaceStr
works perfectly, but
SET @what = table.column
UPDATETEXT @what @TextPointer @OffSet @DeleteLength @ReplaceStr
Dies with 'Incorrect syntax near '@what'' (no matter what @what actually is). Is what im trying to do even possible, or should I give up and hard code the column (which I really dont want to do - I have to update in the region of 60 text fields)?
June 18, 2005 at 12:43 pm
Sounds like you have two problems. First you cannot use @TableVar.column. You must assign an alias to the table variable to be able to do something like TV.Col= 'whatever'.
2nd you seem to want to update multiple columns using dynamic sql. While I don't even know if it's possible with a table var. I suggest you try to find a more static way to do this if you want to avoid sql injection attacks.
June 18, 2005 at 1:47 pm
I'll post the whole relevant section of code so it makes a touch more sense. This section is inside another loop that cycles through tables as assigns the table name as @TableName
SET @ColumnName2 =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('text')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
This line here is what I need to reference as the table/column name
print PARSENAME(@TableName, 1)+'.'+@ColumnName2
IF @ColumnName2 IS NOT NULL
BEGIN
SET NOCOUNT ON
DECLARE @TextPointer varbinary( 16 )
DECLARE @DeleteLength int
DECLARE @OffSet int
SELECT @TextPointer = textptr( @ColumnName2)
FROM PARSENAME(@TableName, 1)
SET @DeleteLength = len( @SearchStr )
SET @OffSet = 0
WHILE ( SELECT count( * )
FROM PARSENAME(@TableName, 1)
WHERE patindex( @SearchStr2, @ColumnName2) 0
) > 0
BEGIN
SELECT @OffSet = patindex( @SearchStr2, @ColumnName2) - 1
FROM PARSENAME(@TableName, 1)
WHERE PATINDEX( @SearchStr2, @ColumnName2) 0
UPDATETEXT @TableName.@ColumnName2 @TextPointer @OffSet @DeleteLength @ReplaceStr
END
SET @TRCTR = @TRCTR + @@ROWCOUNT
END
I dont have to worry about SQL injection or even efficient coding - this is a one-off query to update multiple fields via Enterprise Manager.
June 18, 2005 at 6:44 pm
I've never done anything like this, but I still don't see that code hapenning without dynamic sql. You just can do select ? from @TableNameVar.
Sorry if I can't provide more help.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply