Divide By Zero Error when passing a parameter in Sql Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

HI all i had a question on passing a parametr to a query, i am trying to fileter all the data which has more than 1000 bps i had my query like the below

SQL

select * from tbla 
where id=128
and good='G'
and abs(((impt*rate)/(v1*shares*rate))*10000)>1000

when i write a query like this i am getting result showing the rows which has more than 1000 bps

but when i write a query like this

SQL

declare @id as int 
set @id=128
select * from tbla
where id=@id
and good="G";
and abs(((impt*rate)/(v1*shares*rate))*10000)>1000

the query is booming out saying divide by zero error i dont understand whats the diffrence between both queries accept the parameter part i was trying to google it but no use can some one help me out with this.

Posted 31-Jul-12 5:26am

makwith9789

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-07-31T05:42:00

You can handle it using NULLIF

This is where NULLIF comes to the rescue. In the query below, the divisor will be changed to NULL if it was 0, setting the result of the entire calculation to NULL instead of causing a runtime error.

SQL

SELECT IdCol, Col1 / NULLIF(Col1 + Col2, 0) AS Col1Ratio FROM dbo.SampleTable;

If so desired, a COALESCE function can be added to change this NULL result back to a chosen numeric value – or it can be left as NULL to make it instantly visible that for this row, a value of Col1Ratio could not be computed.

Refer: What if null if null is null null null is null? [^]