I've got a table inside a SQL Server 2000 database which stores SQL claims within an
NTEXT column. I have to read one of these simple values, carry out some substitutes onto it using
UPDATETEXT after which carry it out using
sp_executesql. I am conscious that you cannot declare
NTEXT variables in SQL Server 2000, to workaround i have declared a brief table to keep the worthiness and manipulate it. The final step would be to pass that value to
sp_executesql, however i can't learn how to do this. Representative code to date is below:
/*Create a temp table to store the NTEXT SQL statement*/ CREATE TABLE #temp ( SqlStatement NTEXT ) /*Get the statement*/ INSERT INTO #temp (SqlStatement) SELECT [SqlStatement] FROM [Reports] WHERE ID = @ID -- Format placeholders in statement DECLARE @placeholder VARCHAR(20) DECLARE @placeholderIndex INT SET @placeholder = '@param1' SELECT @placeholderIndex = (CHARINDEX(placeholder, SqlStatement) - 1) FROM #temp /*Get a pointer to the NTEXT field*/ DECLARE @textPtr VARBINARY(16) SELECT @textPtr = TEXTPTR(SqlStatement) FROM #temp IF @placeholderIndex > 0 BEGIN UPDATETEXT #temp.SqlStatement @textPtr @placeholderIndex 7 'paramValue' END /* Get the statement and execute DECLARE @SqlText NTEXT -- This is not possible in SQL 2000 */ exec sp_executesql @SqlText
How do i get and execute the SQL statement? Casting to some varchar would potentially truncate the statement.
NB: I understand this can be a laborious method of doing things the style of the machine has run out of my control.