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.
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 Varcharprint 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;'ENDRegards,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. |
|
|
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 onlyThis is a value in tableRegular Salary Annual Salary0 5500000000 20000000000 20000000 180100002000000000 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
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))+''');' -- etcFROM employee |
|
|
|
|
|
|
|