Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-05 : 07:58:25
|
| Nibedita Saha writes "I am working on the porting of Oracle Database script(PL/SQl) to T-SQL script. I am facing a lot of problem regarding the way to handle predefined exceptions in T-SQL. The errors like No_Data_Found, Too_Many_rows are predefined in oracle. How do I track such predefined errors in T-sql.I would be obliged if I get a solution.Thanks in AdvanceNibedita Saha" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 08:52:38
|
| These are not predefined in SQL Server because they AREN'T errors. You can use the @@ROWCOUNT variable to test how many rows were affected by the last SQL statement you execute (SELECT, INSERT, UPDATE, and DELETE):SELECT * FROM pubs..authorsSELECT @@ROWCOUNT --this will return 23You can put this @@ROWCOUNT value into a variable and test it, and if it exceeds the number of rows you expected, you can use the RAISERROR command to throw an error message:DECLARE @rows intSELECT * FROM pubs..authorsSET @rows=@@ROWCOUNTIF @rows>23BEGINRAISERROR 'There were too many rows in the authors table.', 16, 1ENDIf you do use @@ROWCOUNT, you MUST get its value IMMEDIATELY after the operation you performed, otherwise the next SQL statement will change @@ROWCOUNT.Also, you can use the SET ROWCOUNT option to limit the maximum number of rows affected by an operation.As far as "no data found", you can test for a @@ROWCOUNT=0, or you can use the EXISTS or NOT EXISTS clauses to see if data exists in the table/query:IF NOT EXISTS (SELECT * FROM pubs..authors WHERE au_fname='GGGGGGGGGGG')BEGINRAISERROR 'Last name not found.', 16, 1ENDAll of these functions and statements are documented in Books Online. |
 |
|
|
|
|
|
|
|
SQL SERVER EXCEPTION HANDLING