Link to home
Start Free TrialLog in
Avatar of Mez4343
Mez4343

asked on

Error using FLOAT datatype in Injected Stored Procedure

I have a syntax error below running the query? Is there something special I need when injecting column other than type varchar into a stored procedure? thanks

Msg 8114, Level 16, State 5, Line 20
Error converting data type varchar to float.

SELECT * from FX

declare @Proc nvarchar(50)
declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)
select @RowCnt = 1
select @Proc = 'fx_save'
declare @TransactionName varchar(30) = 'Update FX Rates';

-- get source data to inject into stored Proc
declare @FXImport table (p_fx int,currency1 varchar(50),currency2 varchar(50),rate float(53),
                         date datetime,isdeleted bit,createdby varchar(64),created datetime,updated datetime,updatedby nvarchar(30))
insert into @FXImport (p_fx,currency1,currency2,rate,date,isdeleted,createdby,created,updated,updatedby) select * from fx where isdeleted = 0 order by created desc

select @MaxRows=count(*) from @FXImport
-- loop through source and call SP for each row. SP will recalculate cross rate which is why I am calling SP
BEGIN TRAN @TransactionName
      while @RowCnt <= @MaxRows
      begin
            select @ExecSql = 'exec ' + @Proc + ' ''' + currency1 + ' ''' + currency2 + ' ''' + rate + ' ''' + date + ' ''' from @FXImport
            execute sp_executesql @ExecSql
            SET @RowCnt = @RowCnt + 1
      end
SELECT * from FX
ROLLBACK TRAN @TransactionName
-- COMMIT TRAN @TransactionName
Avatar of Zopilote
Zopilote
Flag of United States of America image

could it be that you are using a comma (,) as a decimal ppoint for currency1 for currency2?
Avatar of Mez4343
Mez4343

ASKER

No, currency 1 and 2 values would be: USD or EUR not money.
ASKER CERTIFIED SOLUTION
Avatar of DFW_Ed
DFW_Ed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial