How to use a variable field name in Dynamic Sql and execute it using sp_executesql?
I am having a difficult time executing a dynamic SQL with sp_executesql.
When I execute the following statements, I get the following error:
Server: Msg 409, Level 16, State 2, Line 5
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a nvarchar data type as an argument.
@RETURN_VALUE = N/A
But If I substitute @My_SP_PhaseReal with one of the actual field namd (Phase05_Real) then it works. Then, that means I have to hard code each field name which are about 117.
Please help.
set @sSQL2 = 'SELECT @My_RealOutput =
Sum(@My_SP_PhaseReal)
FROM tblXfrLoadData_eLAT
WHERE tblXfrLoadData_eLAT.ps_RefDes =@My_SP_RefDes
AND tblXfrLoadData_eLAT.RollUp = 0
AND tblXfrLoadData_eLAT.ps_PwrPhase = @My_SP_PwrPhase
AND tblXfrLoadData_eLAT.Project = @My_SP_ProjFile'
select @ParameterList = N'@My_SP_PhaseReal NVarchar(50),
@My_SP_RefDes NVarchar(50),
@My_SP_PwrPhase NVarchar(4),
@My_SP_ProjFile NVarchar(50),
@My_RealOutput real output'
declare crs_phase cursor for
SELECT Phase
FROM tbl_PhaseName
WHERE Model = @sProjModel + '_xfr'
And PhaseValue = '-1'
for read only
open crs_phase
fetch next from crs_phase into @sPhase
WHILE @@FETCH_STATUS = 0
begin
select @sPhaseImag = @sPhase + '_Imaginary'
Declare Crs_Load CURSOR FOR
SELECT ps_RefDes, ps_PwrPhase
FROM TempResult
WHERE (RollUp = 1)
FOR READ ONLY
open Crs_Load
--begin
fetch next from Crs_Load into @sRefDes, @sPwrPhase --, @SumReal, @SumImag, @SumAmps
WHILE @@FETCH_STATUS = 0
begin
Execute sp_executesql @sSQL2, @ParameterList,
@sPhaseReal,
@sRefDes,
@sPwrPhase,
@sProjFile,
@Sp_TotalReal output
print @sSQL
fetch next from Crs_Load into @sRefDes, @sPwrPhase
end -- crs_load cursor
close Crs_Load
deallocate Crs_Load
fetch next from crs_phase into @sPhase
end --Phase cursor
close crs_phase
deallocate crs_phase
|
