Cannot find either column “dbo” or the user-defined function or aggregate “dbo.udf_RemoveHTMLTags”, or the name is ambiguous

Question

I am trying to create a sql script and run . I am seeing the compiler error Cannot find either column “dbo” or the user-defined function or aggregate “dbo.udf_RemoveHTMLTags”, or the name is ambiguous.On running i get the following error The multi-part identifier "#tempriskdesc.risk_id" could not be bound.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[udf_RemoveHTMLTags]
    (@HTMLText VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT

    SET @Start = CHARINDEX('<', @HTMLText)
    SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
    SET @Length = ( @End - @Start ) + 1

    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
         SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
         SET @Start = CHARINDEX('<', @HTMLText)
         SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
         SET @Length = ( @End - @Start ) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END

I then try to use that function in this manner:

BEGIN TRY
    CREATE TABLE #tempriskdesc
            (risk_id INTEGER ,
             risk_desc VARCHAR(4000))

    INSERT INTO #tempriskdesc
       SELECT 
          p.risk_id ,
          [dbo].[udf_RemoveHTMLTags](SUBSTRING(p.risk_descr, 1, 4000))
       FROM    
          PROJRISK p

    UPDATE PROJRISK
    SET risk_desc = #tempriskdesc.risk_desc
    WHERE #tempriskdesc.risk_id = PROJRISK.risk_id

    DROP TABLE #tempriskdesc
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) ,
            @ErrorSeverity INTEGER ,
            @ErrorState INTEGER ,
            @ErrorNumber INTEGER;
    SELECT  @ErrorMessage = ERROR_MESSAGE() ,
            @ErrorSeverity = ERROR_SEVERITY() ,
            @ErrorState = ERROR_STATE() ,
            @ErrorNumber = ERROR_NUMBER();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH   

Answer 1

At the very least it looks like there is a type-o in the SELECT part of the INSERT given that both the SELECT...INSERT and the UPDATE reference the PROJRISK table yet the SELECT is pulling from the risk_descr column and the UPDATE is writing to a risk_desc column. Is that correct?

Even if there is no type-o in the field names you can still get rid of the temp table and some complexity by reducing the operation to just the UPDATE statement as follows:

UPDATE pr
SET pr.risk_desc = [dbo].[udf_RemoveHTMLTags](SUBSTRING(pr.risk_desc, 1, 4000))
FROM PROJRISK pr

Also, any reason you are using VARCHAR(4000) instead of VARCHAR(8000) or VARCHAR(MAX)? What is the risk_desc field defined as? Perhaps the SUBSTRING is also not necessary?

EDIT:
It is best practice to nearly always specify a column list for an INSERT statement. However, given that the SELECT statement produces the same number of fields as the temp table with the same datatypes and in the same order, this is not the source of your error.

EDIT 2:
The error for "The multi-part identifier "#tempriskdesc.risk_id" could not be bound." is most likely due to missing the FROM clause in your UPDATE query. While that query technically doesn't even need a temp table (see above), the syntax should have been:

UPDATE pr
SET pr.risk_desc = tmp.risk_desc
FROM PROJRISK pr
INNER JOIN #tempriskdesc tmp
        ON tmp.risk_id = pr.risk_id