Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

can stored procedure run more than 1 sql statements

Hi all,
I have 2 independent stored procedures(I cannot join them because of
the group condition are different however they have the same cust id)
but one for reading the summary and other showing the details of the
summary. Now I want to run these two stored procedures in order to
get the both return results for generating a .Net crytral report. Can
I get the results form both stored procedures and how can I do that.
Please help me. Thanks a lot.
Hellosixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello

Hi,

Why not use views for this. You could achieve with that.

-Manoj|||..NET should be able to handle multiple result sets from a single sp.

You can "spell out" both SQL/T-SQL code blocks in the same sp or you
can create two seperate sp's and call sp B from sp A via an EXECUTE
statement.

Good Luck

sixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello|||groups@.rlmoore.net (rm) wrote in message news:<4c61f49.0309191421.14d680a4@.posting.google.com>...
> .NET should be able to handle multiple result sets from a single sp.
> You can "spell out" both SQL/T-SQL code blocks in the same sp or you
> can create two seperate sp's and call sp B from sp A via an EXECUTE
> statement.
> Good Luck
I have try the 2nd method (spA exec spB) but it seem that nothing can
return at the .net crystal report even I do not use the subreport. is
there other methods?
Pls help me.
Thx
Hello

Can SQL break an array into one string? [Stored Procedure]

Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like

SELECT * FROM [eai.Documents] WHERECategoryId=11 ORCategoryId=16 ORCategoryId=18.

My question is: Can I do the same in my stored procedure?
Here is it:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[eai.GetSubCategoriesById](@.Idint)ASdeclare @.pathvarchar(100);SELECT @.path=PathFROM [eai.FileCategories]WHERE Id = @.Id;SELECT Id, ParentCategoryId,Name, NumActiveAdsFROM [eai.FileCategories]WHERE PathLIKE @.Path +'%'ORDER BY Path

Thank you
Artashes

There is no Array in Stored procedure, but you do can it by use dynamic sql . pass stored procedure a string as 11,16,18

then

in store procedure do

exec N'select * from yourTable where id in ' + @.idlist

Hope this help

|||

DavidDu thank you for answer. I found another solution.

It is herehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=475225&SiteID=1

Artashes