Get Data from function in insert

Question

While doing

create table #tmpsr(
srid int,
W_DiffOriginal decimal(12,2)
)
insert into #tmpsr
        (srid,W_DiffOriginal)
        select sr_id,  --- From Table
        W_DiffOriginal=DiffOriginal --- From Function
        From TBL_SR,dbo.fnc_VoucherDetails_Get(sr_id) ---Table-Valued Function
        Where   SRdoid = 12811 --- Column in the table TBL_SR
        and fsrid=sr_id ---fsrid: Columns in the Table-Valued Function,

I Got the message :

Msg 207, Level 16, State 1, Line 9 Invalid column name 'sr_id'.

Any Idea?

Answer 1

i think error is coming because you are calling function in form clause

select .... From TBL_SR,dbo.fnc_VoucherDetails_Get(sr_id) in this case its not able to get what is sr_id

so to resolve this call function in your select statement like this

create table #tmpsr( 
srid int, 
W_DiffOriginal decimal(12,2) 
) 
insert into #tmpsr 
        (srid,W_DiffOriginal) 
        select sr_id,  --- From Table 
        (select W_DiffOriginal from dbo.fnc_VoucherDetails_Get(sr_id))=DiffOriginal ---From Function 
        From TBL_SR, ---Table-Valued Function 
        Where   SRdoid = 12811 --- Column in the table TBL_SR 

this may resolve your issue