Division by zero error in a stored procedure
Hi I have implemented the following stored procedure in a .net web application. Then run the application. I got this error
Division by zero error
Stored procedure:
CREATE procedure Details(@Stringtext varchar(8000),@SearchStringtext varchar(100))
as begin
SELECT ({fn LENGTH(@Stringtext)}-
{fn LENGTH({fn REPLACE(@Stringtext, @SearchStringtext, '')})})/
{ fn LENGTH(@SearchStringtext)}AS String_Count
end
Obviously:
{ fn LENGTH(@SearchStringtext)}
... evaluates to zero.
If SearchStringtext is empty, its length is 0. So the stored procedure tries to divide by zero (which is undefined, which must be done ).
In other words, the next part becomes zero:
{ fn LENGTH(@SearchStringtext)}
You might want to add some logic (if possible operator) to prevent division if SearchStringtext is empty.
The length of SearchStringText is zero, and hence division by zero error exists. Make sure the string is nonzero length when calling the function. Alternatively, check the length before making a selection
{ fn LENGTH(@SearchStringtext)}
evaluates to 0.
However, why is it a stored procedure? Are you not using the db function? If this is not a simplistic problem, all of this (length, replacement, etc.) can be done in your .net application
The only division operation in this procedure is as a divisor. fn LENGTH(@SearchStringtext)
Hence, the length seems **SearchStringtext**
to be zero. You may be trying to find an empty string.
Please check and then clarify the details of the question along with the DB platform.
The length of the SearchStringtext seems to be 0 - so the procedure tries to divide by zero.