Can a stored proc swallow an error

wackyphill

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

Subscribe Post Reply

2841

David Portas

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

wackyphill

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

Simon Hayes

<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

David Portas

> 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

Erland Sommarskog

(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

by: Jen S | last post by:

I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF...

Microsoft SQL Server

Concurrency Handling In Stored Procedures

by: xAvailx | last post by:

I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...

Microsoft SQL Server

C stored Proc problem on AIX

by: Nyul | last post by:

Gurus, I have a verb big problem which I'm unable to explain. We have a DB2 V6.1.0 on AIX 4.3 I want to make a C stored procedure which at the end will be called by a PHP script. The...

DB2 Database

V5R2 DB2/UDB iSeries: can a system trigger call a SQL stored proc?

by: Dave Sisk | last post by:

I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...

DB2 Database

Stored Procedure problem in DB2 for OS/390 V6

by: Rhino | last post by:

I am getting an sqlcode of -927 when I execute SQL within a COBOL stored procedure in DB2 OS/390 Version 6 on OS/390. I have looked at the error message for that condition and tried everything I...

DB2 Database

Calling a parameterized stored proc with adtaper or command obj

by: Eric Land | last post by:

Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I am creating a command object and creating a parametr list, and assigning a value from a session variable (this is working)...

ASP.NET

SQL1131N DARI (Stored Procedure) process has been terminated abnormally

by: ravindrag | last post by:

Hi, I am getting error SQL1131N during sqlj.install_jar(...). There is no useful message in the diag.log (even with diag level 4). I am giving the diag.log entries at the end of this posting...

DB2 Database

Can't find stored procedure - help!

by: db2user24 | last post by:

I'm trying to invoke a DB2 stored procedure. The stored proc is coded in C and compiled to a shared library, which has been placed in the <DB2 dir>/functions directory. The platform is Linux (using...

DB2 Database

handling errors from stored procs

by: mirandacascade | last post by:

Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...

Microsoft Access / VBA

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

Similar topics