display error message - SQL Server Forums

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

Transact-SQL (2000)

display error message

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 12:02:37

im trying to include an error trapping...so that this can be returned, if the record is successfully added and if there is problem inserting it.

i get
sg 2627, Level 14, State 1, Procedure usp_RECEIPIENT, Line 17
Violation of PRIMARY KEY constraint 'PK_Receipient'. Cannot insert duplicate key in object 'dbo.Receipient'.
The statement has been terminated.
Msg 3903, Level 16, State 1, Procedure usp_RECEIPIENT, Line 24
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The 'usp_RECEIPIENT' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

ALTER procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50)

AS
SET NOCOUNT ON
DECLARE @ErrorCode int

IF NOT EXISTS
(select receipient_email from dbo.Receipient
where receipient_email = @receipient_email)
BEGIN TRANSACTION
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)

IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN @ErrorCode;
END

--exec dbo.usp_RECEIPIENT 'TRACEYR@YAHOO.COM','Tracey','Graham','010101'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 12:07:19

the BEGIN TRAN should be outside the IF NOT EXISTS.

DECLARE @ErrorCode int

BEGIN TRANSACTION

IF NOT EXISTS (select receipient_email from dbo.Receipient where receipient_email = @receipient_email)
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)
SET @ErrorCode = @@Error

IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 12:22:21

Is this better
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50)

AS
SET NOCOUNT ON
DECLARE @ErrorCode int

BEGIN TRANSACTION
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)

IF @@ERROR <> 0 GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

When i run it i get which is true im testing an error

Msg 2627, Level 14, State 1, Procedure usp_RECEIPIENT, Line 17
Violation of PRIMARY KEY constraint 'PK_Receipient'. Cannot insert duplicate key in object 'dbo.Receipient'.
The statement has been terminated.
Unexpected error occurred!

But i don't want all the violation bits going to the user developer appliation

What can i return to him...a nice error code perhaps

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 12:26:06

Have you tried the code I suggested?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 12:49:28

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50)

AS

DECLARE @ErrorCode int

BEGIN TRANSACTION

IF NOT EXISTS (select receipient_email from dbo.Receipient where receipient_email = @receipient_email)
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)
SET @ErrorCode = @@Error

IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

--exec

Nothing returns...except Command(s) completed successfully.

Looking for meaningful error for web developer guy.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 13:08:47

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50),
@ErrorCode INT OUTPUT

AS

--DECLARE @ErrorCode int

BEGIN TRANSACTION

IF NOT EXISTS (select receipient_email from dbo.Receipient where receipient_email = @receipient_email)
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)
SET @ErrorCode = @@Error

IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Go

Declare @code int
exec dbo.usp_RECEIPIENT 'TRACEYRAWLINGS@YAHOO.COM','Tracey','Rawlings','010101', @code output
SELECT @code

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 14:13:14

If i add one then another keep getting zero?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 14:20:04

quote:
Originally posted by TRACEYSQL

If i add one then another keep getting zero?

Sorry
I did not understand. Can you rephrase?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 14:45:36

Brand new email.
Add
Declare @code int
exec dbo.usp_RECEIPIENT 'MYNEWEMAIL@YAHOO.COM','Tracey','New','010101', @code output
SELECT @code

The code i get is zero

If i run it again i get zero...(rather than a different error)
The record already exists so i was hoping for a 1 or a error code

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 16:25:00

Yes the default is 0. If you want to find out if the INSERT did not happen

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50),
@ErrorCode INT OUTPUT

AS

--DECLARE @ErrorCode int
/*
ErrorCode Comments
----------------------
0 Insert
1 No-Insert

*/

BEGIN TRANSACTION

--set default value
SET @ErrorCode = 1

IF NOT EXISTS (select receipient_email from dbo.Receipient where receipient_email = @receipient_email)
insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)
SET @ErrorCode = @@Error

IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Go

Declare @code int
exec dbo.usp_RECEIPIENT 'TRACEYRAWLINGS@YAHOO.COM','Tracey','Rawlings','010101', @code output
SELECT @code

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-16 : 19:06:52

I see let me try that tomorrow with the values.
I let you know in morning

Thanks

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-17 : 07:58:03

Still get 0

Here table
Receipient_Email nvarchar(100)
Receipient_LastName nvarchar(100)
Receipient_FirstName nvarchar(100)
Receipient_Work_Phone nvarchar(50)

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 08:07:48

what is the primary key of the table , use select statement with same emailid ,i think u r trying to insert the duplicate primary key in the table

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-17 : 08:55:38

I got it working...and it displays the code and message so that
will be passed to the developer.

Phew that was hard work.
Thanks for your guidance

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter procedure [dbo].[usp_RECEIPIENT]
@receipient_email nvarchar(100),
@receipient_lastname nvarchar(100),
@receipient_firstname nvarchar(100),
@receipient_work_phone nvarchar(50),
@RetCode int = NULL OUTPUT,
@RetMsg nvarchar(100) = NULL OUTPUT

AS
SET NOCOUNT ON

DECLARE @Err int
DECLARE @Rows int
SELECT @RetCode = NULL
SELECT @RetMsg = ''

IF @receipient_email IS NULL
select @RetMsg = 'Email is required ' , @RetCode = 0
IF @RetCode = 0
RETURN

insert into dbo.Receipient (Receipient_Email,Receipient_LastName,Receipient_FirstName,Receipient_Work_Phone)
values(@receipient_email, @receipient_lastname, @receipient_firstname, @receipient_work_phone)

select @Err = @@Error, @Rows = @@RowCount

If (@Err <> 0 ) GOTO HandleErr

If @Rows > 0
SELECT @RetCode = 1, @RetMsg = 'New Receipient Added'
ELSE
SELECT @RetCode = 0, @RetMsg = 'New Receipient Not Added'
RETURN

HandleErr:
SELECT @RetCode = 0,
@RetMsg = ' Run Time Error: ' + CONVERT(NVARCHAR,@Err)
RETURN

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-17 : 09:10:30

Suppress message

In the query analyzer i get this if it works
1 New Receipient Added

If not works
Grid view i see
0 Run Time Error: 2627 (Which is what i wanted)

In the messages tab i see
Server: Msg 2627, Level 14, State 1, Procedure usp_RECEIPIENT, Line 28
Violation of PRIMARY KEY constraint 'PK_Receipient'. Cannot insert duplicate key in object 'Receipient'.
The statement has been terminated.

Is there a way to prevent this doing the messages..............
(im hoping the developer not get this )

Cheers

Finally.

souLTower
Starting Member

39 Posts

Posted - 2009-02-27 : 13:37:00

I don't believe that SQL2000 supports try catch code. That being said you could change your code so that it returns a different int value based on the result. You would need to test that the primary key does not already exist. I have used the following strategy with success in systems where the record may be inserted by a different process than the one I'm using:


declare @ret int
set @ret = 1  -- Success code

INSERT into myTable(val) 
  SELECT top 1 @dataFromProcedure FROM myTable T WHERE 
    (SELECT COUNT(T2.val) from myTable T2 WHERE T2.val = @primaryKey) = 0

set @ret = case when @@rowcount > 0 then 1 else 0 end

This way the update will only happen if the key value does not exist in your table and no other process can add it at the same time. The return value will tell the developer if the update was successful.

God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources