Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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
0
Mez4343
Asked:
Mez4343
1 Solution
 
ZopiloteCommented:
could it be that you are using a comma (,) as a decimal ppoint for currency1 for currency2?
0
 
Mez4343Author Commented:
No, currency 1 and 2 values would be: USD or EUR not money.
0
 
DFW_EdCommented:
can't seem to get my answer to submit.  trying attachment FloatDatatypeAnswer.txt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now