July 29, 2010 at 12:44 am
exec ril_pandl_stock_dtls 'abc',@fin_year,@fin_prdcode
select*
fromril_pandl_stk_valuation_dtl
wherefinyear = @fin_year
andfinperiod = @fin_prdcode
is working fine
create procedure xxxxxxxxxx
begin
exec ril_pandl_stock_dtls 'abc',@fin_year,@fin_prdcode
end
is not working
the line which gives error is
update a
setPLAmt=PLAmt-amt
from#temp1 a, #temp3 b
wherea.ouid =b.ou_id
it shows as b.ou_id as "Invalid Column name"
but its working fine when executed independently .
July 29, 2010 at 7:11 am
[font="Verdana"]create procedure xxxxxxxxxx
begin
exec ril_pandl_stock_dtls 'abc',@fin_year,@fin_prdcode
end
If this is not the complete SProc code then please provide. Or check whether you have declared atleast @fin_year, @fin_prdcode variables.
Thanks,
Mahesh[/font]
MH-09-AM-8694
July 30, 2010 at 11:29 am
Normally when I see a problem with invalid column name in a #Temp table, there will be a table with the same name already created in the same session. If you use the #Temp1, #Temp2 convention a lot then it's very easy to create #Temp1 in the current session, forget about it, and then exec a stored procedure that also creates a table called #Temp1.
It can be a big mystery until you figure out what you did.
Todd Fifield
July 30, 2010 at 11:53 am
1. as Mahesh said - make sure
@fin_year,@fin_prdcode are in the parameter list of your stored proc definition
2. "invalid column Name" usually means Invalid column name
-------------------------------
update a
set PLAmt = PLAmt - amt
from #temp1 a, #temp3 b
where a.ouid = b.ou_id
-------------------------------
so, if the above lines are direct copy paste
=============
where a.ouid = b.ou_id
==========
- check this field names if they are correct.
August 2, 2010 at 4:10 am
le_eli (7/30/2010)
2. "invalid column Name" usually means Invalid column name
Very Good catch:-D
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 3, 2010 at 8:01 pm
Did this problem get resolved yet?
Your temp table column names are different in your where clause a.ouid = b.ou_id.
update a
set PLAmt = PLAmt - amt
from #temp1 a, #temp3 b
where a.ouid = b.ou_id
You should check your create #temp1 and #temp3 to make sure that the columns you referenced are the same spelling. Verify column name spelling for PLAmt and ouid for #Temp1 and that ou_id is defined in #Temp3.
Where is "amt" coming from? I am assuming "amt" is a column in one of the two temp tables, so you need to verify that column spelling as well.
Lastly, you should put your table variable in front of ALL columns to formalize your coding method. Never leave them blank as it is sloppy coding and shows laziness.
Frank
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply