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  |
|
|
|