Trap when SELECT returns >1 row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mscertified - 18 Jul 2008 18:57 GMT

In my sproc, have a SELECT which should always return ONE row.
However, I'd like to trap if returns zero rows or >1 row.
How could I do this using TRY...CATCH ?

Thanks

Reply to this Message

SQL Menace - 18 Jul 2008 19:02 GMT

Why wouldn't you use @@ROWCOUNT instead?

Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

> In my sproc, have a SELECT which should always return ONE row.
> However, I'd like to trap if returns zero rows or >1 row.
> How could I do this using TRY...CATCH ?
>
> Thanks

Reply to this Message

Plamen@sqlstudio.com - 18 Jul 2008 19:50 GMT

Seems you posted this question twice, here is a copy/paste from my
reply to your first one:

Immediately after the select statement you can check the row count and
raise an error that will be trapped in the catch section:

IF @@ROWCOUNT > 1
RAISERROR('More than one rows.', 16, 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message