| Author |
 Topic  |
|
|
divyaram
Posting Yak Master
India
180 Posts |
 Posted - 01/25/2013 : 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 |
Edited by - divyaram on 01/25/2013 07:28:10
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 01/25/2013 : 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. |
 |
|
|
divyaram
Posting Yak Master
India
180 Posts |
Posted - 01/25/2013 : 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 |
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 01/25/2013 : 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
|
|
|
| |
Topic |
|
|
|
want to convertVarchar to float in a dynamic query