Invalid column

Question

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'.

Answer 1

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

Answer 2

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".