data type issue(removing precison)

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

Transact-SQL (2005)

data type issue(removing precison)

Author

Topic

satishk
Starting Member

39 Posts

Posted - 2007-04-27 : 04:02:02

Hi,
I am using the gollowing query
Select 'update env_usage SET quantity='+cast( quantity as varchar(50))+','+' userenteredvalue= '+convert(varchar(1550),userenteredvalue ) +' where sitecode='+''''+sitecode+''''+' and year in (2006) and componentid in ('+cast(componentid as varchar(20)) +') and month='+cast(month as varchar(10))

from backuptables..crg_input_env_usage_20Apr2007

where sitecode='00223TR' and year in (2006) and componentid in (124,125,133,134,247,248,271,

272,278,280,281,283,284)

The result is:
update env_usage SET quantity=969498.91666666700000, userenteredvalue= 1.1634e+007 where sitecode='00223TR' and year in (2006) and componentid in (133) and month=2
I want to have quantity column in following format=969498916666667.00000

result for sp_columns for quantity column is
BackupTables dbo crg_input_env_usage_20Apr2007 Quantity 3 decimal 28 30 14 10 1 NULL NULL 3 NULL NULL 8 YES 106

plz help
satish

satishk
Starting Member

39 Posts

Posted - 2007-04-27 : 04:09:45

userenterdvalue column datatype is float

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 04:31:45

How many times have we told you to stay away from dynamic sql?
Divide the result with 10,000,000 and round it. Also put 1.1634e+007 within atopstrophes.

Peter Larsson
Helsingborg, Sweden

satishk
Starting Member

39 Posts

Posted - 2007-04-27 : 05:02:24

Following modified quesry gave me the disired result

Select 'update env_usage SET quantity='+cast( quantity as varchar(50))+','+' userenteredvalue= '+cast(convert(decimal(16,0),userenteredvalue) as varchar(200)) +' where sitecode='+''''+sitecode+''''+' and year in (2006) and componentid in ('+cast(componentid as varchar(20)) +') and month='+cast(month as varchar(10))

from backuptables..crg_input_env_usage_20Apr2007

where sitecode='00223TR' and year in (2006) and componentid in (124,125,133,134,247,248,271,

272,278,280,281,283,284)

satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 05:41:43

You haven't told us why you still feel the need for dynamic sql.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 05:48:57

Look how much nicer and cleaner the code is when not using dynamic sql

update		e
SET		e.quantity = @quantity,
		e.userenteredvalue = @userenteredvalue
from		env_usage as e
where		e.sitecode = @sitecode
		and e.year = 2006
		and ',' + @ComponentID + ',' LIKE '%,' + e.componentid + ',%'
		and e.month = @month

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources