How to Convert varchar to float

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to Convert varchar to float

Author

Topic

anandbe21
Starting Member

4 Posts

Posted - 2009-10-05 : 21:49:14

Hi All,

i need to convert varchar value to float value here ismy query

Declare @qrystr varchar(8000)
Declare @Leavedetails float

Begin

set @Leavedetails ='select NoofDaysToEncash from tbl_leavedetails where empid =''+@EmpId+'' and NoofDaysToEncash <> 0'

Set @qrystr = 'Select case: when tbl_UserTypeMaster.UserType = ''Service'' then tbl_RankMaster.RankName+'' ''+tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname else
tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname +'' ''+ tbl_RankMaster.RankName end ''Name'',tbl_LeaveTypeMaster.LeaveType,tbl_LeaveStatistics.LeaveStatisticsId,
tbl_LeaveStatistics.EmpId, tbl_LeaveStatistics.LeaveTypeId,tbl_LeaveStatistics.FromYear,tbl_LeaveStatistics.ToYear,
(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0)) - ''+@Leavedetails+'' ''NoofDays''

ineed to +@Leavedetails+ convert varchar to float value and how to append and send this variable in sqlserver 2000 during cast or convert function.

Regards
ANAND

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-06 : 00:51:46

I don't understand your use of dynamic SQL here, same goes for your other topic. Neither topic shows any reason to use dynamic SQL, so you will probably be able to avoid cast/convert too.

By the way, be very careful with the use of float data type. You typically want to use decimal instead due to the inaccuracy of floating point data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

anandbe21
Starting Member

4 Posts

Posted - 2009-10-07 : 15:07:59

Hi All,

i hd still showing error mssg like
Error convert varchar to float.

my queryis this
ALTER Proc SP_Get_LeaveStatistics
(
@UserTypeId uniqueidentifier = null,
@EmpId uniqueidentifier =null,
@LeaveStatisticsId uniqueidentifier = null,
@LeaveTypeId uniqueidentifier = null,
@FromYear datetime = null,
@ToYear datetime = null,
@Granted float = -2,
@AlreadyUsed float = -2,
@CurrentMaxCarry float = -2,
@CurrentMaxEncash float = -2,
@ModifiedBy uniqueidentifier = null,
@ModifiedDate datetime = null,
@Flag int = 1
)
As

Declare @qrystr varchar(8000)
Declare @Leavedetails float

Begin

set @Leavedetails = 10

Set @qrystr = 'Select case: when tbl_UserTypeMaster.UserType = ''Service'' then tbl_RankMaster.RankName+'' ''+tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname else
tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname +'' ''+ tbl_RankMaster.RankName end ''Name'',tbl_LeaveTypeMaster.LeaveType,tbl_LeaveStatistics.LeaveStatisticsId,
tbl_LeaveStatistics.EmpId, tbl_LeaveStatistics.LeaveTypeId,tbl_LeaveStatistics.FromYear,tbl_LeaveStatistics.ToYear,
(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0) - @Leavedetails) ''NoofDays'',tbl_LeaveStatistics.Granted,isnull(tbl_LeaveStatistics.AlreadyUsed,0) ''AlreadyUsed'',
tbl_LeaveStatistics.CurrentMaxCarry,tbl_LeaveStatistics.CurrentMaxEncash,tbl_LeaveStatistics.ModifiedBy,tbl_LeaveStatistics.ModifiedDate,
tbl_LeaveStatistics.Flag

from
tbl_LeaveStatistics,tbl_leavedetails,tbl_LeaveTypeMaster,tbl_EmpMaster,tbl_UserTypeMaster,tbl_RankMaster

i hd doubt here in this line

(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0) - @Leavedetails) ''NoofDays''

hw i need to cast @Leavedetails any query complte statement abut this query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 16:08:29

This is really bad code. You are joining multiple tables together yet you don't include any join conditions. You are therefore getting a cross join (cartesian product) amongst all of those tables. I seriously doubt that's what you want.

If @LeaveDetails is going to equal 10, then you should use tinyint data type instead of float.

Also, you should never prefix a stored procedure with sp_ for performance reasons.

Here is your code rewritten, notice that there is no need for dynamic SQL (@qrystr), you still need to fix the cross joins though:


CREATE Proc Get_LeaveStatistics 
( 
@UserTypeId uniqueidentifier = null, 
@EmpId uniqueidentifier =null, 
@LeaveStatisticsId uniqueidentifier = null, 
@LeaveTypeId uniqueidentifier = null, 
@FromYear datetime = null, 
@ToYear datetime = null, 
@Granted float = -2, 
@AlreadyUsed float = -2, 
@CurrentMaxCarry float = -2, 
@CurrentMaxEncash float = -2, 
@ModifiedBy uniqueidentifier = null, 
@ModifiedDate datetime = null, 
@Flag int = 1
) 
As 

Declare @Leavedetails tinyint

set @Leavedetails = 10

Select 
	case 
		when tbl_UserTypeMaster.UserType = 'Service' then tbl_RankMaster.RankName+' '+tbl_EmpMaster.EmpName + ' '+ 
			tbl_Empmaster.Surname 
		else tbl_EmpMaster.EmpName + ' '+ tbl_Empmaster.Surname +' '+ tbl_RankMaster.RankName 
	end AS Name,
	tbl_LeaveTypeMaster.LeaveType,
	tbl_LeaveStatistics.LeaveStatisticsId, 
	tbl_LeaveStatistics.EmpId, 
	tbl_LeaveStatistics.LeaveTypeId,
	tbl_LeaveStatistics.FromYear,
	tbl_LeaveStatistics.ToYear, 
	(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0) - @Leavedetails) AS NoofDays,
	tbl_LeaveStatistics.Granted,
	isnull(tbl_LeaveStatistics.AlreadyUsed,0) AS AlreadyUsed, 
	tbl_LeaveStatistics.CurrentMaxCarry,
	tbl_LeaveStatistics.CurrentMaxEncash,
	tbl_LeaveStatistics.ModifiedBy,
	tbl_LeaveStatistics.ModifiedDate, 
	tbl_LeaveStatistics.Flag 
from tbl_LeaveStatistics,tbl_leavedetails,tbl_LeaveTypeMaster,tbl_EmpMaster,tbl_UserTypeMaster,tbl_RankMaster

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources