Solved

Error using FLOAT datatype in Injected Stored Procedure

Posted on 2010-11-19
3
367 Views
Last Modified: 2012-05-10
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
Comment
Question by:Mez4343
3 Comments
 
LVL 5

Expert Comment

by:Zopilote
ID: 34175833
could it be that you are using a comma (,) as a decimal ppoint for currency1 for currency2?
0
 
LVL 10

Author Comment

by:Mez4343
ID: 34176448
No, currency 1 and 2 values would be: USD or EUR not money.
0
 
LVL 3

Accepted Solution

by:
DFW_Ed earned 250 total points
ID: 34177240
can't seem to get my answer to submit.  trying attachment FloatDatatypeAnswer.txt
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
SQL Syntax: How to force case sensitive query? 2 44
TSQL Challenge... 7 35
SQL trigger 5 21
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question