By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Can a stored proc swallow an error

P: n/a
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?

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
--
Jul 23 '05 #2

P: n/a
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.

Jul 23 '05 #3

P: n/a

<wa********@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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?


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-handl...ml#client-code
http://www.sommarskog.se/error-handling-I.html#ADO.Net

Simon
Jul 23 '05 #4

P: n/a
> Yes, I could check for the records existance before hand.

Not beforehand - in the INSERT statement itself.
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.


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
--
Jul 23 '05 #5

P: n/a
(wa********@yahoo.com) writes:
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?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.