Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 want to convertVarchar to float in a dynamic query

Author  Topic 

divyaram
Posting Yak Master

180 Posts

Posted - 2013-01-25 : 06:57:35
HI Team,
I do have a column as float in a table ,which i will calling it in dynamic query .while calling it in dynamic query float is getting convert as Varchar.I Attaching the query which i used.

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Name VARCHAR(250)
DECLARE @AnnualIncome float--VARCHAR(250)
DECLARE @RegularIncome float--VARCHAR(250)
DECLARE @DisplayName VARCHAR(250)
DECLARE @UID VARCHAR(250)



DECLARE employee_cursor CURSOR FOR



select Name,AnnualIncome,RegularIncome,DisplayName,UId
from employee


OPEN employee_cursor

FETCH NEXT FROM employee_cursor INTO @Name,@AnnualIncome,@RegularIncome,@DisplayName,@UID

PRINT 'DECLARE'
PRINT ' UID NUMBER;'
PRINT 'BEGIN'
PRINT Char(9)
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'BEGIN '

PRINT ' select User_typeid into UID from T_Employee where Name = ''' +@Name +';'
PRINT ' insert into t_UserTable (UID,Name,AnnualIncome) values (UID, '''+ @Name+ cast(@AnnualIncome as varchar) + ''');'
PRINT ' insert into t_UserTable (UID,Name,RegularIncome) values (UID, '''+ @Name+ cast(@RegularIncome as varchar) + ''');'
PRINT ' EXCEPTION WHEN OTHERS THEN '
PRINT ' DBMS_OUTPUT.put_line (''Name Not found for: ' + @Name + ''');'
PRINT 'END; '
PRINT Char(9)
print @AnnualIncome----//comments// Here we can see @AnnualIncome as Varchar
print convert(float,@AnnualIncome)--//comments// here we are trying to convert it to float but still it is showing as Varchar
FETCH NEXT FROM employee_cursor INTO @Name,@AnnualIncome,@RegularIncome,@DisplayName,@UID
END

CLOSE employee_cursor
DEALLOCATE employee_cursor

PRINT 'END;'

END


Regards,
Divya

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 07:56:10
What is the problem you are running into? CONVERT(float,@AnnualIncome) should convert it to float - what are you observing that leads you to believe it is not ocnverting?

What environment are you running this in? Some of the syntax in the dynamic query you are constructing doesn't seem like it is MS SQL.

Also, it seems to me that you don't need to use a cursor if the only goal is to generate a set of dynamic SQL statements.
Go to Top of Page

divyaram
Posting Yak Master

180 Posts

Posted - 2013-01-25 : 08:39:53
Hi james,
Thanks for the reply....
This is a sample query only.. orginal may more complication so put only sample data.you are correct the destination where i have to execute the dynamic Sql is oracle. I am having the input in sql server. so finding issue over there in MS sql only
This is a value in table

Regular Salary Annual Salary
0 550000000
0 2000000000
0 2000000
0 18010000

2000000000 is a float value in input table so while executing the sql im getting it as '2e+009' .


quote:
Originally posted by James K

What is the problem you are running into? CONVERT(float,@AnnualIncome) should convert it to float - what are you observing that leads you to believe it is not ocnverting?

What environment are you running this in? Some of the syntax in the dynamic query you are constructing doesn't seem like it is MS SQL.

Also, it seems to me that you don't need to use a cursor if the only goal is to generate a set of dynamic SQL statements.



Regards,
Divya
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 09:17:47
The 2e+009 is the string representation that you are seeing. If you want to see in decimal notation, you can convert to decimal (for example, decimal(19,0).

You can avoid the cursor and write one query - something like this:
SELECT
'select User_typeid into UID from T_Employee where Name = ''' + [Name] + ';'
+ 'insert into t_UserTable (UID,Name,AnnualIncome) values ('''+[UID] +''','''
+ [Name] + ''','''+ cast(@AnnualIncome as VARCHAR(32))+''');'
-- etc
FROM
employee
Go to Top of Page
   

- Advertisement -