Home > IT Answers > SQL Server > SQL Server equivalent of Oracle’s “when no data found” exception
CO Bill
120 pts.
 SQL Server equivalent of Oracle’s “when no data found” exception
SELECT statement, Stored Procedure variables, Stored Procedures
In a stored procedure, I'm storing the result of a select statement in @variable. Is there a way to capture a SQL Code 100, like Oracle's SQLERRM(100)/ORA-01403 if no records are returned? I'm currently setting @variable = '*' before the select and and IF @variable = '*' afterwards, but there must be a more elegant way - isn't there?

Software/Hardware used:
ASKED: August 15, 2008  2:41 PM
UPDATED: March 6, 2012  5:44 PM
RELATED QUESTIONS
Answer Wiki:
After the select is executed check the value of the @@ROWCOUNT system variable. If it is = 0 then there were no records found. You can then use the RAISERROR system function to through a message saying this which would be returned to the client.
Last Wiki Answer Submitted:  September 9, 2008  9:23 am  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

Denny Cherry
 64,520 pts.
 

if no records are there it will return one row and with null value.

Faizkazi4u
 10 pts.