SQL Procedure Error

[ratzp]

I have written a Stored Procedure of around 50 lines...

Well while executin it manually it does not give any error
but while executin it via Application it executes a part of it but not in full....

how to find out the Error if its occuring

Ratz

 

[JD1866DSC]

Firstly is there any error trapping in the proc, if not add some like this


This should folow every bit of code
SELECT
@Error = @@ERROR
,@NumRows = @@ROWCOUNT
,@ErrorMsg = 'Enter appropriate message'
IF @Error > 0 GOTO Error

then add this at the bottom of the proc

Error: -- Errors have been detected, continue and exit cleanly
SET NOCOUNT OFF
RAISERROR ('Proc Failed: %s Error Message: %s ErrorNo: %d Num of rows: %d',16,1,'ProcName',@ErrorMsg,@Error,@NumRows)
RETURN 1 -- exit returning status value of 1 (to indicate failure)
GO

I would also run SQL Profiler.

Hope this helps

 

[ratzp]

Thanks for the post....

I dont know what exactly the error is .....

For eg... If I insert a Duplicate entry which is a primary key column
SQL gives Primary Key Violation Error
with error line number etc... It takes the values from system.dbo.sysmessages


I want the same message to appear when the error occurs

 

[SQLDenis]

read here

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ptheriault]

If this is SQL Server 2005 use the CATCH method to get your error. Here is a link that explains it.

http://www.4guysfromrolla.com/ASPScripts/PrintPage.asp?REF=/webtech/041906-1.shtml

- Paul
- Database performance looks fine, it must be the Network!