Float rounding issue involving sum

Try this in MS SQL:
create table #TEST (val1 float, val2 float)
insert into #TEST(val1, val2) values(0.0002, 0.0048)
select ROUND(val1 + val2, 2), val1+val2, ROUND(0.005,2)  from #TEST
drop table #TEST

val1 = 0.0002
val2 = 0.0048

The above query will return:
ROUND(val1 + val2, 2) = 0
val1 + val2 = 0.005
ROUND(0.005,2) = 0.010


It seems not just sum and rounding, even the multiplication itself will have issue


Dunno how to solve this yet, if u know, pls let me know.
Thanks.

Find a string within stored procedure

To find string content inside stored procedure, you can use this query:

Declare @StringToSearch varchar(100)
SET @StringToSearch = '%something%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type <> 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name

Different types in sysobjects:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

source: here

To find the column names from tables, you may use this query

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%colname%'
ORDER BY schema_name, table_name;

or use this query:

SELECT Table_Schema, Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_name in ( select name from sysobjects
where xtype = 'U' )
and column_name like '%colname%'
order by table_schema, table_name

.net timeout

.net default timeout when querying database (eg. calling stored procedure etc) is 30 seconds!
Please refer here.