Handling Errors in Stored procedure (Use RAISERROR But how we show the Our error to User)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Handling Errors in Stored procedure (Use RAISERROR But how we show the Our error to User)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-26 : 09:41:39

Dhanraj Patil writes "Hi ,

I have a knowledge on @error object in SQL Server and
RASISERROR also .

I have Create the SP like this,

Definatio of Test table

create table test (Id1 int not null,Name1 varchar(10))

create procedure spTest
As
insert into Test (Name1)values ('prasad')
if @@error<>0
begin
raiserror('Insert Err',10,1)
end

But i want only My Message not "SQL Server Error message "
Please tell me how to do it in VB and ASP .

I already run this using bellow code

Dim conn As New ADODB.Connection
Dim str As String
str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=temp;Data Source=PRATIKSHA_PC"
conn.Open str

conn.Execute " spTest "

It gives me SQL Server Error :

Cannot insert the value NULL into column 'Id1', table 'temp.dbo.test'; column does not allow nulls. INSERT fails.

Becaue in Test table "Id1" Column does not allow null
but i want my message only "Insert Error"

Please help me i am waiting for reply....

Thanks ,

Dhanraj"

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2001-11-26 : 09:54:31

Sorry, I do not think you can ovverride the SQL Server error.

*************************
Just trying to get things done

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-11-26 : 11:14:38

I'm not sure whether this is being called from ASP or a VB app, but in either case you should be able check for the error on that side and then call your own error handling.

shyamprasad76
Starting Member

38 Posts

Posted - 2001-11-26 : 11:42:09

quote:

Then, waht is the use of having raise error with custom defined error in SQL swerver, if the same canot be seen from the front-end application like ASP, VB.

Thanks,

Shyam.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-26 : 14:20:44

SQL Server errors are handled independently of the data access method used (ODBC, OLE DB, etc.) You can always write your SQL Server procedures to handle the errors internally, and then use @@ERROR and RAISERROR to create your own custom error messages.

Plus, you can see SQL Server errors by using the Errors collection of an ADO Connection object (this is not the same as the Err object in VB). Check the ADO documentation for more info on the Errors collection.

shyamprasad76
Starting Member

38 Posts

Posted - 2001-11-27 : 09:31:46

Hi,

Use 'Return' statement along with an variable (eg return @var1) to halt the program execution and handle it back to the calling problem. Now in the VB code try getting the 'var' value from the 'RETURN' to identify that there is an error.

Now, depending on the error number you can raise the error message in the VB/VBScript code.

BTW: As told by ROB, Sql server errors are ment to be used internally and they are independent of the provider used.

Hope this helps.

Thanks,

Shyam.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources