Solved

Error using FLOAT datatype in Injected Stored Procedure

Posted on 2010-11-19
3
366 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can i make query faster by writing T-SQL? 6 37
SQL Server syntax question 13 32
Email Header Detail 12 55
Access 2003 query lost it's only join 7 27
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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