Can a stored proc swallow an error

[wackyphill@yahoo.com]

I have a table I insert a record into to give access to a user. It uses
primary keys so duplicates are not allowed, so trying to add a record
for a user more than once is not allowed.

In my .NET programs, sometimes it's easier to let the user select a
group of people to give access to eben if some of them may already have
it.

Of course this throws an exception an an error message. Now I could
catch and ignore the message in .NET for this operation but then I'm
stuck if something is genuinely wrong.

So is there a way to do this? :
In my stored procedure determine if an error occured because of a
duplicate key and somehow not cause an exception to be returned to
ADO.NET in that case?

[David Portas]

Re: Can a stored proc swallow an error

Can't you just change the INSERT statement so that it won't insert duplicate
rows? For Example:

INSERT INTO foo (user, ...)
SELECT 'Smith', ...
WHERE NOT EXISTS
(SELECT *
FROM foo
WHERE user = 'Smith') ;

or

INSERT INTO foo (user, ...)
SELECT user, ...
FROM bar
LEFT JOIN foo
ON foo.user = bar.user
WHERE foo.user IS NULL
AND ... ;

--
David Portas
SQL Server MVP
--

[wackyphill@yahoo.com]

Re: Can a stored proc swallow an error

Yes, I could check for the records existance before hand. But I wanted
to know if you can detect when an error occurs in T-SQL, and handle it
w/o causing an exception to be thrown in ADO.NET.

[Simon Hayes]

Re: Can a stored proc swallow an error


<wackyphill@yah oo.com> wrote in message
news:1104775581 .290491.75140@f 14g2000cwb.goog legroups.com...[color=blue]
>I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
>
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
>
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
>
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?
>[/color]

Unfortunately, error handling in MSSQL (at least up to version 2000) is
somewhat limited - see these articles for more details:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

These sections in particular may be useful for you:

http://www.sommarskog.se/error-handling-II.html#client-code

http://www.sommarskog.se/error-handling-I.html#ADO.Net

Simon

[David Portas]

Re: Can a stored proc swallow an error

> Yes, I could check for the records existance before hand.

Not beforehand - in the INSERT statement itself.
[color=blue]
> I wanted
> to know if you can detect when an error occurs in T-SQL, and handle it
> w/o causing an exception to be thrown in ADO.NET.[/color]

See the articles that Simon posted but IMO a stored procedure that requires
you to ignore an error for correct inputs is not a good stored procedure -
it won't fail safe and real problems may go undetected.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Can a stored proc swallow an error

(wackyphill@yah oo.com) writes:[color=blue]
> I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
>
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
>
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
>
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?[/color]

In SQL 2000, no. In the next version of SQL Server, SQL 2005 currently
in beta, yes.

But there is really not that big difference between catching the error in
SQL or in .Net. In the .Net excrption you ignore if the error number 2627
or else you rethrow. But admittedly, it's nicer to do this in the SQL
code, since you keep the error-handling logic closer to the test.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp