SQL Server equivalent of Oracle’s “when no data found” exception

sql oracle exception equivalent

wabregoc · Jan 28, 2015 · Viewed 7k times · Source

I need SQL Server equivalent of Oracle’s “when no data found” exception example:

EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN 0;
      WHEN OTHERS
      THEN
         RETURN 0;

converter to sql server

Answer

use @rowcount to see how many records came out from a select statment.

select @l_emp_name = emp_name from employees where employee_id = 1313;

if @@rowcount > 1 ---in oracle this means TOO_MANY_ROWS Print 'too many rows!!'

if @@rowcount = 0 ---in oracle this means NO_DATA_FOUND Print 'too many rows!!'

if @@rowcount = 1 ---in oracle this means yaaaay!! one record there Print 'got 1 records, happy days!'