Share via

How to write a code if exists code inside try catch block

Rakesh kumar 106 Reputation points
2020-10-07T12:58:06.893+00:00

How to write a code if exist object inside a code try catch block when am trying write a code its not working.Please find below a code as per my requirement and when i am trying keep a code before if exist its not throwing error.

Create PROCEDURE dbo.error
@paramet int = 0
AS

if exists (select * from sysobjects where id = object_id('[dbo].[tmp_error_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table dbo.error

if exists (select * from sysobjects where id = object_id('[dbo].[tmp_error_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table dbo.error

if @paramet = 1

select column1 into dbo.error from Table1 where Id in (
select distinct Id from table1 where Active = 'N'

--Id---
delete from dbo.error where inv_ctl_nbr in (
select distinct Id from dbo.error where Id= 1)

else ------Here am getting error 'Incorrect syntax error 'else'

BEGIN TRY
exec dbo.error @paramet = 1

END TRY
BEGIN CATCH
------SET NOCOUNT ON
------exec dbo.spGetErrorInfo
Print ERROR_MESSAGE()

END CATCH

if exists (select * from sysobjects where id = object_id('[dbo].[tmp_error_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [dbo].[tmp_error_1]

if exists (select * from sysobjects where id = object_id('[dbo].[tmp_error_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [dbo].[tmp_error_1]
Go

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments No comments

Answer accepted by question author

Viorel 127K Reputation points
2020-10-07T13:05:41.42+00:00

Try adding BEGIN and END:

. . .
if @paramet = 1
BEGIN
   select column1 into dbo.error from Table1 where Id in (
      select distinct Id from table1 where Active = 'N' )

   --Id---
   delete from dbo.error where inv_ctl_nbr in (
      select distinct Id from dbo.error where Id= 1)    
END
else
. . .

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,786 Reputation points
    2020-10-07T13:23:31.623+00:00

    You keep asking the same question and getting the same answer.

    Your if exists code is checking for a table named "tmp_error_1", but then you are running "drop table dbo.error". Is this correct?

    The query analyzer is smart enough to not error if you are checking for the same table and will not error:

    if exists (select * from sysobjects where id = object_id('[dbo].[tmp_error_1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
    drop table tmp_error_1
    

    What exactly are you trying to do? The code you have posted multiple times, makes no sense. Please describe in words exactly what you are trying to do.

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.