Re: SQL Errors in Stored Procedures

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 04/24/04 

Date: Sat, 24 Apr 2004 13:06:02 +1000

T-SQL / SQL Server currently have no way of doing this.

Another option not yet suggested is to create a data access layer between
the app & SQL Server to provide such functionality.

Personally, I don't like Zach's idea of performing the lookup before doing
the insert as this can have large performance implications, even if the
check only seeks against the PK. I prefer to design applications so that
there's a very low possibility of key conflict & then handling exceptions on
the rare occassion rather than ensuring that every single insert suffers the
performance penalty of a lookup on the PK index. But then again, each to
their own!

Regards,
Greg Linwood
SQL Server MVP

"Zach Wells" <no_zwells_spam@ain1.com> wrote in message
news:%23sOdW3WKEHA.2776@TK2MSFTNGP12.phx.gbl...
> gb wrote:
>
> > Is there a way to "turn off" error messages in a stored procedure so
that they don't get sent to the calling procedure? I have a VB.NET app that
executes a stored procedure to insert some records. Currently, whenever you
try to insert a duplicate record, the stored procedure throws it back to the
VB.NET app as an exception. I would like to handle the error in the stored
procedure and return the error number instead of the exception being thrown.
I can save the @@error value in a variable and return it, but currently the
exception gets thrown also. I know I can handle the exception at the VB.NET
app level, but I would prefer to handle this in a more graceful way.
> >
> > Thanks for any help.
>
> Checking for the existence of the record prior to doing the insert would
> be the more graceful way of handling this situation.
>
> Zach

Relevant Pages

  • Re: Just for kicks, try this
    ... Robbe Morris - 2004-2006 Microsoft MVP C# ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... app using ado.net execution time of some stored procedures has increased ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Authentication problem continued
    ... This is the exception I get if on my test box if I haven't run ... I have a SQL Server login established now and I can log in on my ... SQL Server is configured for Windows Authentication only and not ... I'm still having trouble getting my app to authenticate correctly. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: MSDE Slow in executing Stored Procedures
    ... App that creates an ADO connection object and execute a simple ... stored procedure and its equivalent insert statement. ... MSDE 7.0) vs. SQL Server 2000. ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Stored procedure doesnt return dataset unless recompiled
    ... Have you changed any of the objects called by the stored procedure? ... SQL Server will not allow you to change the ... "Dewey" wrote in message ... >I have a web app that calls a SP. ...
    (microsoft.public.dotnet.framework.adonet)