Case 1:

SELECT ROUND(CONVERT(FLOAT,'0.030') * CONVERT(FLOAT,'0.045'),4)

Result for above query is 0.013 which is wrong.

It should give 0.014 instead of 0.013

Case 2:

To overcome above scenario I have tried below query:

SELECT ROUND(CONVERT(DECIMAL(37,4),'0.030') * CONVERT(DECIMAL(37,4),'0.045'),4)

Then result is 0.014 which is right.

But it fails for below values:

SELECT ROUND(CONVERT(DECIMAL(37,4),'0.0011') * CONVERT(DECIMAL(37,4),'0.045'),4)

The Result is 0.0001 which is wrong.

It should give 0.0000

Can anyone suggest me why all these scenarios are taking place?

What should I have to use to overcome above scenarios?

Thank you

By using  (37,4) you are unnecessarily reserving space for values to the left of the decimal point that you don't appear to need. 

0.0011 * 0.045 = 0.0000495 but the datatype of the result is DECIMAL(38,6) which rounds it to 0.000050 first before the ROUND function sees it.

The following works as desired for both of your cases as the smaller precision allows more to be used in the scale of the result - which is DECIMAL(13,8)

SELECT CAST(ROUND(CONVERT(DECIMAL(6, 4), '0.030') * CONVERT(DECIMAL(6, 4), '0.045'), 4) AS DECIMAL(6, 4)),
       CAST(ROUND(CONVERT(DECIMAL(6, 4), '0.0011') * CONVERT(DECIMAL(6, 4), '0.045'), 4) AS DECIMAL(6, 4)) 

In addition to Martin's post: the reason you get the wrong result can be understood if you run this query:

SELECT CONVERT(FLOAT,'0.030') * CONVERT(FLOAT,'0.045')

That is, if you run it in SSMS, you will see 0.00135, why it seems that SQL Server needs to clean up its act. However, if you run the same query from SQLCMD, you will get the result 1.3499999999999999E-3 and when rounded to four decimals, the result should absolutely be 0.0013 - or at least in the close vicinity. Float is an approxamite data type, at least seen from the perspective of 10 as a base. With 2 as a base, it is certainly exact. Not all decimal numbers can be represented exactly with 2 as a base, which can be demonstrated by running this in SQLCMD:

select convert(float, '0.00135')

This returns 1.3500000000000001E-3. SSMS again returns 0.00135.

Why this difference between SQLCMD and SSMS? The query tools receives the float numbers from SQL Server and for presentation, they need to convert the numbers to strings. For this purposes, they use different libraries; SQLCMD is a native-mode application, whereas SSMS is written in .NET. And the two libraries have a different level of ambition on how much to show of those last bits in the number.

All this ballet is nothing which is unique to SQL Server, but applies to any computer environment where you use floating-point numbers.