SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 want to convertVarchar to float in a dynamic query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divyaram
Posting Yak Master

India
180 Posts

Posted - 01/25/2013 :  06:57:35  Show Profile  Reply with Quote
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

3706 Posts

Posted - 01/25/2013 :  07:56:10  Show Profile  Reply with Quote
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

India
180 Posts

Posted - 01/25/2013 :  08:39:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/25/2013 :  09:17:47  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000