CODEWITHSUNDEEP
sql-server
Simran
Simran

Reputation: 569

Invalid Column name for a Temporary table column in a stored procedure

I have a stored procedure inside of which a temporary table is created:

I get an error that says:

Invalid column name 'ValFromUser'.

Why is that? Why do I get this error only for ValFromuser but no other column?How I may get rid of this?

CREATE Procedure [dbo].[OutputProcedure]

--declarations here 
AS
BEGIN
SET NOCOUNT ON 

IF OBJECT_ID('tempdb.dbo.##Temp2') Is null
Begin
create table ##Temp2
(
Rownumber int not null,
ValFromUser nvarchar(30),
Percentage decimal(18, 4) not null
);

select * 
from dbo.ResultsStored
join (
    select 
        Rownumber,
        (SUM(Percentage) / @cnt) as Perc 
    from ##Temp2 
    GROUP BY 
        Rownumber, ValFromUser
) t -- Invalid column Name ValFromUser error here
    on dbo.ResultsStored.RowId = t.Rownumber
    and dbo.ResultsStored.HashedKey = HASHBYTES('MD5', @StringConcat)
end

End



Insert into dbo.ResultsStored( searchSerial,FinalSearchSeral, StringSearched, RowId,PercentMatch, HashedKey) 
select @searchNumber, @searchNumber, dbo.encrypt(@StringConcat), RowNumber, (SUM(Percentage)/@cnt) as Percentage , HASHBYTES('MD5', @StringConcat)
FROM ##Temp2 GROUP BY RowNumber, ValFromUser --Here

end

Upvotes: 2

Views: 8908

Answers (3)

Kristianne Nerona
Kristianne Nerona

Reputation: 755

I also encountered this. I had a temp table in my sp that was getting the error:

Invalid column name <column name>

I was only running the stored procedure when I am encountering this. However, the stored procedure is run on a query window where I was also testing the code (for another bug) where I declared the temp table but without the the erroring fields specified (for brevity).

When I renamed the sp's temp table, the problem disappeared. So, I am guessing that the sp is calling this temp table instead of the one that I specified in the sp.

Upvotes: 1

ps_prakash02
ps_prakash02

Reputation: 583

1: Try a selecting ##Temp2 table and check whether you are getting the ValFromUser column displayed. If not drop the ##Temp table and execute your above statements again.

2: Remove the Comma (,) after the last column from "Percentage decimal(18, 4) not null," The 

create table ##Temp2
(
Rownumber int not null,
ValFromUser nvarchar(30),
--ColumnName nvarchar(30),
--ValFromFunc decimal(18, 4),
--FuncWeight decimal(18, 4),
Percentage decimal(18, 4) not null
);

3: Include ValFromUser Column in the select statement

Select Rownumber, ValFromUser, (SUM(Percentage) / @cnt) as Perc 
FROM ##Temp2 
GROUP BY Rownumber, ValFromUser

Try the above steps and let me know whether it solves your problem or not ?

Upvotes: 1

Monkawee Maneewalaya
Monkawee Maneewalaya

Reputation: 150

At the last line of create table statement. Remove comma(,) and reply your result again.

create table ##Temp2
(
Rownumber int not null,
ValFromUser nvarchar(30),
--ColumnName nvarchar(30),
--ValFromFunc decimal(18, 4),
--FuncWeight decimal(18, 4),
Percentage decimal(18, 4) not null
);

Upvotes: 1

Related Questions