Invalid column

SVA 116 Reputation points
2024-05-29T11:43:16.77+00:00

Hi

When I execute below SP gives me invalid column error. Sometime it will work and sometime it gives this error

create proc test

as

Begin

Select * into #Temp from table1

begin

	ALTER TABLE #Temp

		ADD IsOn BIT NOT NULL DEFAULT(1)

	UPDATE #Temp SET IsOn = 0

	INSERT INTO #Temp

	EXEC GetDisplay @d, 0, NULL

	UPDATE #Temp SET URL = isnull(URL, ''), TypeId = 7 WHERE IsOn = 1



end

error throws is this

Msg 207, Level 16, State 1, Procedure testLine 262 [Batch Start Line 221]

Invalid column name 'IsOn'.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,149 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 113.6K Reputation points MVP
    2024-05-29T21:44:46.7666667+00:00

    Adding a column to a table, and then updating in the next statement, typically does not work, because the UPDATE statement is compiled before the column is added. But it may work sometimes here, because it's a temp table. (If this sounds confusing to you, it is because it is confusing.)

    Change the code to read:

    Select *, IsOn = convert(bit, 1) into #Temp from table1
    
    0 comments No comments

  2. LiHongMSFT-4306 29,191 Reputation points
    2024-05-30T02:37:10.6633333+00:00

    Hi @SVA

    When the procedure is compiled, the columns do not exist, which means adding IsOn is in the same batch as the reference to use IsOn. That is why you got this error.

    You could use dynamic SQL for that or split them in two procedures.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.