try and catch not working - 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 2005 Forums

Transact-SQL (2005)

try and catch not working

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-17 : 04:56:55

Hi,
i try to working with exception handling with try and catch, but its
not working, pls check my procedure,below iam sending table and procdure

table
USE [smartsystem]
GO
/****** Object: Table [dbo].[proctesting] Script Date: 11/17/2010 15:25:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[proctesting](
[idu] [bigint] IDENTITY(1,1) NOT NULL,
[testwork] [varchar](5) NULL,
CONSTRAINT [PK_proctesting] PRIMARY KEY CLUSTERED
(
[idu] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

sp

USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[std_refmstdetail] Script Date: 11/17/2010 11:56:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[dotext]
(

@testwork int

)
as
set nocount on

BEGIN TRY
begin transaction

insert into proctesting(testwork)
values(@testwork)

commit transaction
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg ='workbring',
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

exec dotext 1,dd

Desikankannan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-17 : 05:04:30

There was nothing to execute because you are proving more input parameters as expected, hence the try catch isn't doing anything.

No, you're never too old to Yak'n'Roll if you're too young to die.

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-17 : 06:14:39

quote:
Originally posted by webfred

There was nothing to execute because you are proving more input parameters as expected, hence the try catch isn't doing anything.

No, you're never too old to Yak'n'Roll if you're too young to die.

pls iam getting, i wants to work with try and catch method in sql
pls check the above code

Desikankannan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-17 : 06:23:59

quote:
Originally posted by desikankannan

quote:
Originally posted by webfred

There was nothing to execute because you are proving more input parameters as expected, hence the try catch isn't doing anything.

No, you're never too old to Yak'n'Roll if you're too young to die.

pls iam getting, i wants to work with try and catch method in sql
pls check the above code

Desikankannan

I am not sure whether it will work or not. Try it;

Begin Try
exec dotext 1,dd --It will raise error
End Try
Begin Catch
Print ERROR_MESSAGE()
End catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources